Forum Discussion
Excellove15
May 24, 2024Iron Contributor
Power query transformations to lookup dates from one table into the other
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...
Excellove15
May 30, 2024Iron Contributor
Hi,
Many thanks for your quick reply
Thanks for this amazing solution.
I will implement this and get back to you!
Thanks
Many thanks for your quick reply
Thanks for this amazing solution.
I will implement this and get back to you!
Thanks
Lorenzo
Jun 16, 2024Silver Contributor
> 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...