topic Re: Aggregate Value Another Table in Desktop

Aggregate Value Another Table

vfernandes0408 — Mon, 05 Mar 2018 19:41:39 GMT

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

Re: Aggregate Value Another Table

Anonymous — Mon, 05 Mar 2018 19:54:14 GMT

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.

Re: Aggregate Value Another Table

vfernandes0408 — Mon, 05 Mar 2018 20:01:02 GMT

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

Re: Aggregate Value Another Table

Anonymous — Mon, 05 Mar 2018 20:14:51 GMT

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

Maybe I didn't get you right?

Re: Aggregate Value Another Table

vfernandes0408 — Mon, 05 Mar 2018 20:54:59 GMT

this function works but i explain wrong.

i make image with explain

https://photos.google.com/share/AF1QipNi1Eh8BrzUck0uYkiNFBcbF3hyp7p20Qb8M5PY2jfunWGDugApQXKp4mDqDNVChw?key=UjVRdElCSnA1cV9mY0hnUGV1M2dhYmxubmo3ZVZB

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

Re: Aggregate Value Another Table

Anonymous — Tue, 06 Mar 2018 19:18:20 GMT

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"

Re: Aggregate Value Another Table

vfernandes0408 — Wed, 07 Mar 2018 17:23:22 GMT

thanks a lot.

I find some errors but i correct and now works fine