Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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

BusinessNameColumn1Values1Column2Values2 
ab companylamp120lamp1 10 
cd companylamp230lamp3 20 
ef companylamp320lamp2 30 
gh companylamp410lamp440 

 

to

BusinessNamelamp1lamp2lamp3lamp4
ab company30   
cd company 3020 
ef company 3020 
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

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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"

vjaywmsft_0-1659518034966.png

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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"

vjaywmsft_0-1659518034966.png

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
HotChilli
Super User
Super User

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'

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.