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"
I try to explain with more details.
i have a table 1 where I have type test1, test2 and test3 and in table2 I have type test2 and test3. I need a formula that if the columns of date, value, and type columns do not have the value in table1 it takes on table2 and can not repeat the value of table1
Hi again
I post you the result I got with my query in the Query Editor:
Maybe I didn't get you right?
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"thanks a lot.
I find some errors but i correct and now works fine