Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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
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
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'
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |