Forum Discussion
Solved
Aggregate Value Another Table
Hi, i need aggregate two table. I need get value on table 2 if dont exist information on table 1 and mount table 3 with aggregate values https://photos.app.goo.gl/zGldGWhb8uN8erYf1
Something like that?
Here is the code for this:
let Source = Table.NestedJoin(Table1,{"Date"},Table2,{"Date"},"Tabelle2",JoinKind.LeftOuter), #"Expanded {0}" = Table.ExpandTableColumn(Source, "Tabelle2", {"Date", "Value", "Type"}, {"Tabelle2.Date", "Tabelle2.Value", "Tabelle2.Type"}), #"Added Custom" = Table.AddColumn(#"Expanded {0}", "Table", each if [Tabelle2.Date] = null then "Table1" else "Table2"), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Value", "Table"}) in #"Removed Other Columns"
this function works but i explain wrong.
i make image with explain
If the date 01/04/2018 doesnt have on table2 i get value on table 1
Something like that?
Here is the code for this:
let
Source = Table.NestedJoin(Table1,{"Date"},Table2,{"Date"},"Tabelle2",JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(Source, "Tabelle2", {"Date", "Value", "Type"}, {"Tabelle2.Date", "Tabelle2.Value", "Tabelle2.Type"}),
#"Added Custom" = Table.AddColumn(#"Expanded {0}", "Table", each if [Tabelle2.Date] = null then "Table1" else "Table2"),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Value", "Table"})
in
#"Removed Other Columns"