Forum Discussion
Pivoting two column with same value
Hi Team,
Good Day !
I am trying to pivot multiple columns Column1 with Values1 and Column2 with Values2. However when I'm pivoting Column2 it gives me an error that Column2 data has already existing column since it has similar data from Column1 which I pivoted first. I'm thinking if they have the same data maybe there is a way two sum it up if they are on similar data.
For example
| BusinessName | Column1 | Values1 | Column2 | Values2 | |
| ab company | lamp1 | 20 | lamp1 | 10 | |
| cd company | lamp2 | 30 | lamp3 | 20 | |
| ef company | lamp3 | 20 | lamp2 | 30 | |
| gh company | lamp4 | 10 | lamp4 | 40 |
to
| BusinessName | lamp1 | lamp2 | lamp3 | lamp4 |
| ab company | 30 | |||
| cd company | 30 | 20 | ||
| ef company | 30 | 20 | ||
| gh company | 50 |
Can this be done? If the pivoting have the same data in column1 and column2 the value should be sum up however if I pivoted column 2 it throws an error that the column is already existing. How can I achieved this ? thanks in advance for your help.
Cheers
John
Hi Anonymous ,
Please refer the code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxSSM7PLUjMq1TSUcpJzC0wBNJGBjCOApBhCOIpKMXqRCslp6CpNgLSxjDVxgowvRDVqWloqo2RzTZSgOmFqE7PQFNtArMbxjGBKo4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BusinessName = _t, Column1 = _t, Values1 = _t, Column2 = _t, Values2 = _t, #" " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"BusinessName", type text}, {"Column1", type text}, {"Values1", Int64.Type}, {"Column2", type text}, {"Values2", Int64.Type}, {" ", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Values2", " "}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Column"}, {"Values1", "Values"}}) in #"Renamed Columns"Pbix as attached.
Best Regards,
Jay
2 Replies
Duplicate the query.
In one version, remove columns (col2, value2). In the other, remove columns (col1, values1) and rename the columns to be column1, values1.
Append the queries and then do the Pivot with aggregate of 'Sum'
Hi Anonymous ,
Please refer the code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxSSM7PLUjMq1TSUcpJzC0wBNJGBjCOApBhCOIpKMXqRCslp6CpNgLSxjDVxgowvRDVqWloqo2RzTZSgOmFqE7PQFNtArMbxjGBKo4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BusinessName = _t, Column1 = _t, Values1 = _t, Column2 = _t, Values2 = _t, #" " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"BusinessName", type text}, {"Column1", type text}, {"Values1", Int64.Type}, {"Column2", type text}, {"Values2", Int64.Type}, {" ", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Values2", " "}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Column"}, {"Values1", "Values"}}) in #"Renamed Columns"Pbix as attached.
Best Regards,
Jay
