Aggregate Value Another Table

[vfernandes0408]

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

[Anonymous]

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.

[vfernandes0408]

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

[Anonymous]

Hi again
I post you the result I got with my query in the Query Editor:

Maybe I didn't get you right?

[vfernandes0408]

this function works but i explain wrong.

i make image with explain

https://photos.google.com/share/AF1QipNi1Eh8BrzUck0uYkiNFBcbF3hyp7p20Qb8M5PY2jfunWGDugApQXKp4mDqDNVC...

If the date 01/04/2018 doesnt have on table2 i get value on table 1

[Anonymous · Accepted solution]

Hi @vfernandes0408

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"

[vfernandes0408]

thanks a lot.

I find some errors but i correct and now works fine