Pivoting two column with same value

[Anonymous]

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

[v-jayw-msft · Accepted 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

[HotChilli]

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'