PowerQuery - Add rows to a table from 2nd table, when row in 2nd not exists in 1st

user_568121 0
Viewed 45

I have a case where I would need to merge 2 tables or add anyhow records from a second table to a main table when the row in the 2nd is not existing in the main. To explain, I have a series of hours recorded by month/year records, not all months are present, while for having clean dataseries I would need ALL months, if recorded with the correct hours, otherwise a row with 0. Something like:

This is the recording table    This is the complete table       This should be the outcome
WO  ID  Date       Hrs          WO  ID  Date       Hrs          WO  ID  Date    Hrs
A   1   01-01-20    4           A   1   01-01-20    0           A   1   01-01-20    4
A   1   01-02-20    8           A   1   01-02-20    0           A   1   01-02-20    8
A   2   01-03-20    3           A   1   01-03-20    0           A   1   01-03-20    0
A   2   01-04-20    4           A   1   01-04-20    0           A   1   01-04-20    0
B   1   01-01-20    6           A   1   01-05-20    0           A   1   01-05-20    0
B   1   01-02-20    2           A   1   01-06-20    0           A   1   01-06-20    0
B   1   01-03-20    3           A   1   01-07-20    0           A   1   01-07-20    0
B   6   01-02-20    8           A   1   01-08-20    0           A   1   01-08-20    0
B   6   01-03-20    5           A   1   01-09-20    0           A   1   01-09-20    0
B   6   01-04-20    3           A   1   01-10-20    0           A   1   01-10-20    0
B   6   01-05-20    7           A   1   01-11-20    0           A   1   01-11-20    0
B   6   01-06-20    4           A   1   01-12-20    0           A   1   01-12-20    0
                                A   2   01-01-20    0           A   2   01-01-20    0
                                A   2   01-02-20    0           A   2   01-02-20    0
                                A   2   01-03-20    0           A   2   01-03-20    3
                                A   2   01-04-20    0           A   2   01-04-20    4
                                A   2   01-05-20    0           A   2   01-05-20    0
                                A   2   01-06-20    0           A   2   01-06-20    0
                                A   2   01-07-20    0           A   2   01-07-20    0
                                A   2   01-08-20    0           A   2   01-08-20    0
                                A   2   01-09-20    0           A   2   01-09-20    0
                                A   2   01-10-20    0           A   2   01-10-20    0
                                A   2   01-11-20    0           A   2   01-11-20    0
                                A   2   01-12-20    0           A   2   01-12-20    0
                                B   1   01-01-20    0           B   1   01-01-20    6
                                B   1   01-02-20    0           B   1   01-02-20    2
                                B   1   01-03-20    0           B   1   01-03-20    3
                                B   1   01-04-20    0           B   1   01-04-20    0
                                B   1   01-05-20    0           B   1   01-05-20    0
                                B   1   01-06-20    0           B   1   01-06-20    0
                                B   1   01-07-20    0           B   1   01-07-20    0
                                B   1   01-08-20    0           B   1   01-08-20    0
                                B   1   01-09-20    0           B   1   01-09-20    0
                                B   1   01-10-20    0           B   1   01-10-20    0
                                B   1   01-11-20    0           B   1   01-11-20    0
                                B   1   01-12-20    0           B   1   01-12-20    0
                                B   6   01-01-20    0           B   6   01-01-20    0
                                B   6   01-02-20    0           B   6   01-02-20    8
                                B   6   01-03-20    0           B   6   01-03-20    5
                                B   6   01-04-20    0           B   6   01-04-20    3
                                B   6   01-05-20    0           B   6   01-05-20    7
                                B   6   01-06-20    0           B   6   01-06-20    4
                                B   6   01-07-20    0           B   6   01-07-20    0
                                B   6   01-08-20    0           B   6   01-08-20    0
                                B   6   01-09-20    0           B   6   01-09-20    0
                                B   6   01-10-20    0           B   6   01-10-20    0
                                B   6   01-11-20    0           B   6   01-11-20    0
                                B   6   01-12-20    0           B   6   01-12-20    0

Is there any way to accomplish this or a similar outcome, so that a record for each combination exists ? Also PowerBI is an option (DAX).

thanks

powerbi powerquery
2 Answers

Not terribly elegant but effective... Create a 'seed' table something like:

WO ID Hours
A  1  0
A  2  0
B  1  0
B  2  0

Then create a new table (Modeleing / New Table) of relevant dates (since you mention DAX):

Dates = Calendar("1/1/2020","1/12/2020")

Finally, create the table of all dates as yet another new table using the crossjoin function (which creates all combinations of records in the joined tables):

BaseTable = CROSSJOIN(Dates,seed)

The BaseTable is then your 'complete' table in the question statement. You can create a calculated column concatenating WO, ID, and Date in both BaseTable and the table with actual hour information, join on that key column in the relationships view, then create a new column in the BaseTable taking the max of hours from the base table and hours from the related table.

Comment back if this does not make sense.

Note that this is a 'quick and dirty' approach because the number of combinations is small. If your actual 'key columns' are more complex or change from time to time, I would consider using generation functions, potentially with externalized parameters, to come up with something like the 'seed' table.

user_13809760

Here it is....

DECLARE @HoursRecorded AS TABLE (WO CHAR(1), ID INT, [Date]  DATE, Hrs INT)

DECLARE @HoursAll AS TABLE (WO CHAR(1), ID INT, [Date]  DATE, Hrs INT)

DECLARE @HoursAllWithRecorded AS TABLE (WO CHAR(1), ID INT, [Date] DATE, Hrs INT)

INSERT INTO @HoursRecorded
    SELECT 'A',   1,   '01-01-20', 4
    UNION SELECT 'A',   1,   '01-02-20',    8
    UNION SELECT 'A',   2,   '01-03-20',    3 
SELECT * FROM @HoursRecorded

INSERT INTO @HoursAll
    SELECT 'A',   1,   '01-01-20', 0
    UNION SELECT 'A',   1,   '01-02-20',    0
    UNION SELECT 'A',   1,   '01-03-20',    0
    UNION SELECT 'A',   1,   '01-04-20',    0
    UNION SELECT 'A',   1,   '01-05-20',    0
    UNION SELECT 'A',   1,   '01-06-20',    0
    UNION SELECT 'A',   1,   '01-07-20',    0
    UNION SELECT 'A',   1,   '01-08-20',    0
    UNION SELECT 'A',   1,   '01-09-20',    0
    UNION SELECT 'A',   1,   '01-10-20',    0
    UNION SELECT 'A',   1,   '01-11-20',    0
    UNION SELECT 'A',   1,   '01-12-20',    0
    UNION SELECT 'A',   2,   '01-01-20',    0
    UNION SELECT 'A',   2,   '01-02-20',    0
    UNION SELECT 'A',   2,   '01-03-20',    0
    UNION SELECT 'A',   2,   '01-04-20',    0
    UNION SELECT 'A',   2,   '01-05-20',    0

SELECT * FROM @HoursAll

INSERT INTO @HoursAllWithRecorded
    SELECT AllH.WO, AllH.ID, AllH.Date, ISNULL(RecH.Hrs, AllH.Hrs) 
    FROM @HoursAll AllH
            LEFT JOIN @HoursRecorded RecH ON AllH.WO = RecH.WO AND AllH.ID = RecH.ID AND AllH.Date = RecH.Date

SELECT * FROM @HoursAllWithRecorded

The last insert statement is your actual query to generate the desired data set and it's quite simple. Rest are to prepare the example data.

user_119825450
Related