Forum Discussion
Power query transformations to lookup dates from one table into the other
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?
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!
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
- Hi,
Many thanks for your quick reply
Thanks for this amazing solution.
I will implement this and get back to you!
Thanks
