Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm struggling to fine the answer to this solution in the forums and online. For context, I'm using a Project Management Software and majority of the Project information is stored in the Project Table in individual columns. However, there are some custom fields which are stored as a list.
I have no problem expanding these but as you know, this creates multiple rows for the same project and ruins the relationships in my model. I would like to see Project.customFields.label as their own columns and the Project.customFields.value as the values in those columns.
Adding to the complexity to this however is there are roughly 10 of these Project.customFields.label fields that would need to be their own columns, and when you expand the list a new row is only created if there is a value stored in that field.
Any help or guidance would be appreciated. I added a screenshot below from excel to help show what I'm looking for assuming the Project Manager field was one of these other fields from the list and there was no value on that paticular project record.
Solved! Go to Solution.
One approach would be to separate the table into two tables: one with lists, and one without lists. In the list table, expand the lists, pivot columns, and then append it to the table without lists. Do you have sample data you could share?
Proud to be a Super User!
One approach would be to separate the table into two tables: one with lists, and one without lists. In the list table, expand the lists, pivot columns, and then append it to the table without lists. Do you have sample data you could share?
Proud to be a Super User!
Unfortunatly the data is proprietary so I don't think I can easily share. I tired above to provide what I could but if you are thinking about something else or have another idea please let me know.
I have also tried to expand the list as it's own table but the problem that I run into when I do this is the Project Number (my unique identifer) doesn't populate in the list table so I have no way to pivot it then merge and append (added some screenshots to show this). Let me know if that makes sense or not.
Taking list to it's own table
Expanded to new rows - Project Number is not in this table and as such cannot merge and append
Try creating a custom column "Is List" in Power Query using this expression:
if Value.Is([Project.customFields], type list)
then 1
else 0
The "Is List" column will enable you to separate the table into two tables: one with lists, and one without lists. In the list table, expand the lists, pivot columns, and then append it to the table without lists.
Proud to be a Super User!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
26 |
User | Count |
---|---|
97 | |
87 | |
43 | |
40 | |
35 |