Help combining rows - keep getting error message

[smatsushima1]

I need to combine the G, H, and I rows together because the first three G, H, and I rows have values in the last two columns but need to be updated to include the data in the last two rows.

I used this post to help me: https://community.powerbi.com/t5/Desktop/Combining-rows-based-on-unique-id-and-combining-information...

Basically unpovited the first column, then pivoted the newly created attribute column to the value column and didn't aggregate it. I ended up with this:

So I ended up with my desired result except that the names are now gone. Why is this and how do I remedy the situation? Thank you for all help.

[v-haibl-msft]

@smatsushima1

I tried with your table and post provided above but didn’t experience the issue you mentioned. Please refer to my Power Query which pasted from Advanced Editor to see if there is any difference with yours. You can also try the solution provided by KGrice.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Herbert\Desktop\Power BI\Help combining rows - keep getting error message.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Dealer Code", Int64.Type}, {"Dealer Name", type text}, {"Feature A", Int64.Type}, {"Feature B", Int64.Type}, {"Feature C", Int64.Type}, {"Feature D", Int64.Type}, {"Feature E", Int64.Type}, {"Feature F", Int64.Type}, {"Feature G", Int64.Type}, {"Feature H", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Dealer Code", "Dealer Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"

Best Regards,

Herbert

[KGrice · Accepted solution]

Hi @smatsushima1. Sometimes in pivot operations there are just too many options for the pivot and you end up with this error. That usually means we'll have to find a workaround, and I'm not sure you need to pivot based on your shown dataset. Are those all of your columns, and the only options are 1/0? If so, while in the Query Editor go to the Transform tab and click Group By (far left).

You'll want to group by Dealer Code and Dealer Name, and then add one column for each existing Feature column, defined as the Max of the column.

I can't expand the window in the picture, but if you scrolled down you'd see the same setup for Features G and H. Doing this will get you to the same end result, but without the errors.