Need Help

Hello all, I have the following problem. I have 2 tables/databases and would like to ad the values here. The tables are built the same only with the difference that I have partly different v...

Need Help

Anonymous

3 years ago

Hi PascalCLE

You can merge them in power query, you can put the following code in Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJBpI6SmZgljGQZQpmmQBZFmCWKZBlDmYBGQbYGLEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Table1 = _t, Qty1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table1", Int64.Type}, {"Qty1", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Table1"}, #"Table (2)", {"Table"}, "Table (2)", JoinKind.FullOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Table", "Qty"}, {"Table (2).Table", "Table (2).Qty"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table (2)",{{"Table1", Order.Ascending}})
in
    #"Sorted Rows"

Then create a measure in the new table

Measure = SUM('Table (3)'[Qty1])+SUM('Table (3)'[Table (2).Qty])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sample.pbix32 KB

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJBpI6SmZgljGQZQpmmQBZFmCWKZBlDmYBGQbYGLEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Table1 = _t, Qty1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table1", Int64.Type}, {"Qty1", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Table1"}, #"Table (2)", {"Table"}, "Table (2)", JoinKind.FullOuter), #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Table", "Qty"}, {"Table (2).Table", "Table (2).Qty"}), #"Sorted Rows" = Table.Sort(#"Expanded Table (2)",{{"Table1", Order.Ascending}}) in #"Sorted Rows"

Forum Discussion

Need Help