Grouping by a column then pivoting values to avoid duplicates and null values.

[sscollins]

I have a specific use case related to how our data comes into Power BI from our source. Our table dumps all custom fields into one column and their values into another. I need these columns pivoted to have a column for each field, but I also need them to be by their Project ID. For example:

Project ID	CustomFieldName	CustomFieldValue
Project_A	Code	12345
Project_A	Location	California
Project_A	Manager	John Doe
Project_B	Code	12346
Project_B	Location	Nevada
Project_B	Manager	Jane Doe

The project ID repeats, and so what I want for the transformed data to be is:

Project ID	Code	Location	Manager
Project_A	12345	California	John Doe
Project_B	12346	Nevada	Jane Doe

Thank you all for the help!

[HotChilli]

Sample data and show desired result please.

[HotChilli]

can you post the 'advanced editor' code and i'll have a look?

[HotChilli]

You can (using the interface) Group By 'Project Id'.  Select only one aggregation : All rows.  Call the column "all".

Then add a custom column:

Table.Pivot([all], {"Code","Location","Manager"} ,"CustomFieldName", "CustomFieldValue")

and you can expand from the column header to return the appropriate fields

[Whibley92]

Hello, I am having a similar issue as below - i merge by person id and add custom column but when i expand from column header it undoes the grouped by. Before there were 3 of the same id and the new line converts the grouped by to 2 lines the same id. I'm guessing its something to do with null values and some values being empty. Not sure how to fix.

[sscollins]

Hi, thanks for the help. I attempted this and this was my experience: Grouping helped in that there was only one row per project ID, and the custom column and formula worked and produced another column. But upon splitting out the three fields I want, the grouped column "all" becomes ungrouped, all the lines come back and I am left with the same null fields in most of the expanded column. I tested how this affected my dashboard and the custom fields are still not connecting with eachother. Any thoughts?