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 values in it.

Here once an example:

but currently only this is displayed:

How do I get the visual to show everything and not filter on the same number (4 and 5)?

Thanks already once for your help
Gr Pascal

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

5 Replies

mlsx4
Memorable Member
3 years ago
Hi PascalCLE
Since both tables have the same structure, why don't you append them in Power Query Editor? And then, you sum
PascalCLE
New Member
3 years ago
I have obtained the results by Measure.
Tabel 1 I get the Qty duch a Datumsfilder the 6 monarte in the past looks and Tabel 2 in that I look in the future.
But I must have the sum of both measures.
this also works in principle with :
Proposition 3 =
MAX(0 ,[Proposal]) + MAX(0, [Proposal 2])
But I am just missing the other values as I have already written
- mlsx4
  Memorable Member
  3 years ago
  In this case, if they are calculated maybe a combination of UNION (https://learn.microsoft.com/es-es/dax/union-function-dax) and some table manipulation function would work
PascalCLE
New Member
3 years ago
Unfortunately, this is not the solution to the problem.

Simply appending at the bottom does not work in this case because I need to see both the one and the other value as shown in the example.

Anonymous

Not applicable

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

Forum Discussion

Need Help