Power query transformations to lookup dates from one table into the other

[Excellove15]

Hi @SergeiBaklan sir,

I have invoice detail table as below:

Also, I have EM - Misc Charge table that don't have date column:

But these 2 tables have 'DBName-Point_Id' , 'DBName-Id' & 'Charge' as a common columns.

I need to find the dates for each rows of 'EM - Misc Charge' table from invoice detail table for their corresponding 'DBName-Point_Id' , 'DBName-Id' & charge using power query transformations(upstream).

I am struggling to achieve this. we had a idea of appending these tables but that would increase the number of rows as we have millions of rows in our original data. This may cause a performance issue.

PFA file here Budget vs Accruals.pbix

Please let me know if you need further details

Thanks in advance!

[#2]

@Excellove15 

I need to find the Dates for each rows of 'EM - Misc Charge' table from 'Invoice Detail' table for their corresponding [DBName-Point_Id] , [DBName-Id] & [Charge]

If you Merge tables 'EM - Misc Charge' with 'Invoice Detail' on the above common keys/columns Left Outer:

then Expand the [Date] field from the [Invoice Detail] merged table:

you should get what you want or am I missing something?

[#3]

Hi @Lorenzo 

Apologise for the delay in getting back to you!

Many thanks for this amazing solution!

Just to confirm, 

what if we wanted to combine both (EM - Misc Charge & Invoice Detail) tables into single table(all data from both table) by looking up at the dates from Invoice Detail table?

Is it possible to append both tables and also lookup the date in a single step?

Thanks in advance!

@Lorenzo @SergeiBaklan 

[#4]

Hi @Excellove15 

what if we wanted to combine both (EM - Misc Charge & Invoice Detail) tables into single table(all data from both table) by looking up at the dates from Invoice Detail table?

To do it with the User Interface only:

• Do a 1st merge as previously described
• Do a 2nd merge (as a new query), selecting table 'Invoice Detail' first then table 'EM - Misc Charge' + their common keys & choose a Left Anti Join Kind

• In the new/second query delete column [EM - Misc Charge] (contains Tables of nulls)
• Append the 2 queries 

[#5]

Hi,

Many thanks for your quick reply
Thanks for this amazing solution.
I will implement this and get back to you!

Thanks

[#6]

@Excellove15 

> 2 week ago I will implement this and get back to you!

If you have a challenge with the implementation, let us know 🙂

In the meantime re. what if we wanted to combine both (EM - Misc Charge & Invoice Detail) tables into single table(all data from both table) by looking up at the dates from Invoice Detail table? ... Is it possible to ...lookup the date in a single step?

With the Advanced Editor this could be something like:

let
    SingleStep = Table.Combine(
        {
            Table.ExpandTableColumn(
                Table.NestedJoin(
                    #"EM - Misc Charge", {"DBName-Point_Id", "DBName-Id", "Charge"},
                    #"Invoice Detail",   {"DBName-Point_Id", "DBName-Id", "Charge"},
                    "Invoice Detail", JoinKind.LeftOuter
                ),
                "Invoice Detail", {"Date"}
            ),
            Table.RemoveColumns(
                Table.NestedJoin(
                    #"Invoice Detail",   {"DBName-Point_Id", "DBName-Id", "Charge"},
                    #"EM - Misc Charge", {"DBName-Point_Id", "DBName-Id", "Charge"},
                    "EM - Misc Charge", JoinKind.LeftAnti
                    ),
                    {"EM - Misc Charge"}
            )
        }
    )
in
    SingleStep

Enjoy it...