Forum Discussion
Pivoting Columns
I have a table with an Custom Field ID and a Custom Value field which is actually a text field. I have another field that is a Client ID. A single client can have more than one row of various Custom Field IDs. What I want to do is pivot the table to have ONE ROW per Client ID with each individual Custom Field becoming a column so that I can merge it with a Client File and bring in all their custom fields. An example of what I am trying to do is below. When I use the basic Pivot command I get a "value" of "1" in the field where I actually want the text field. Any help would be appreciated.
Current Table Layout Custom Field ID Custom Field Value Client ID 106 A 13456 107 CAS 13456 108 SSFT 13456 | What I want Client ID 106 107 108 13456 A CAS SSFT |
5 Replies
CLick on the Gear icon for the Pivot Column step in Power Query, then expand Advanced options, and tell it "Don't Aggregate."
So I do get it to work with the "Don't Aggregate" option, but I still end up with multiple rows for the Client ID, then Columns of 106, 107, 108. I just now get NULLS in in the Columns that do not have data. Am I doing something wrong to whereby I cannot get just a single row for a client with the all the columns associated with the ID in one row, whether NULL or not? Something like below. Once done, the column headings of 106, 107 and 108 are appropriately renamed.
Client ID 106 107 108
13456 A CAS SSFT
Never mind, got your solution to work. Thanks!
Refer, if this can help
