Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi , @Razorbx13
As mentioned by @edhans , in advanced options, you can select "Don't Aggregate."
Best Regards,
Community Support Team _ Eason
Refer, if this can help
https://radacad.com/pivot-and-unpivot-with-power-bi
Hi , @Razorbx13
As mentioned by @edhans , in advanced options, you can select "Don't Aggregate."
Best Regards,
Community Support Team _ Eason
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!
CLick on the Gear icon for the Pivot Column step in Power Query, then expand Advanced options, and tell it "Don't Aggregate."
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |