Forum Discussion
Combine two columns with same name from different table in visualisation
I have two sets of table as below:
| cust_name | A | B | C |
| John | 5 | 5 | 3 |
| Mary | 3 | ||
| Ken | 4 |
| cust_name | C | D | E |
| John | 2 | 1 | |
| Amy | 1 | 4 | |
| Julia | 6 | 1 |
How can I combine and sum the column C in visualisation as below:
| cust_name | A | B | C | D | E |
| Amy | 1 | 4 | |||
| John | 5 | 5 | 5 | 1 | |
| Julia | 6 | 1 | |||
| Ken | 4 | ||||
| Mary | 3 |
The two original tables were formed based on different level of breakdown and the level of breakdown does not work parallel with each other. Some of the breakdown will be coincidentally having the same name at a particular level. Thus, is that possible not the append the table, but applying the summation with the same name at the visualization level? Can it also be applied to the same scenario each time i change the level of breakdown?
after appending the queries for tables,you can create a dax query to summarise the data for the same name
HI Anonymous,
You can also try to use unpivot columns and pivot column function to achieve your requirement.
Sample: create new blank query to store transformed records.
let Source = Table.Combine({Table.UnpivotOtherColumns(#"Table1",{"cust_name"}, "Attribute", "Value"),Table.UnpivotOtherColumns(#"Table2",{"cust_name"}, "Attribute", "Value")}), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Attribute]), "Attribute", "Value", List.Sum) in #"Pivoted Column"Regards,
Xiaoxin Sheng
3 Replies
after appending the queries for tables,you can create a dax query to summarise the data for the same name
Hi Hardik, is there any other alternative without appending the table?
HI Anonymous,
You can also try to use unpivot columns and pivot column function to achieve your requirement.
Sample: create new blank query to store transformed records.
let Source = Table.Combine({Table.UnpivotOtherColumns(#"Table1",{"cust_name"}, "Attribute", "Value"),Table.UnpivotOtherColumns(#"Table2",{"cust_name"}, "Attribute", "Value")}), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Attribute]), "Attribute", "Value", List.Sum) in #"Pivoted Column"Regards,
Xiaoxin Sheng