Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
Hi @vfernandes0408
I don't know if I get your question right, but here is an answer,
You could merge the 2 tables (1+2) by using the Query Editor.
The code is:
let Source = Table.NestedJoin(Tabelle1,{"Date", "Value", "Type"},Tabelle2,{"Date", "Value", "Type"},"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", "Type", "Table"}) in #"Removed Other Columns"
Please tell, if the code is ok for you.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |