Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
nicksutherland
Frequent Visitor

How to expand a list to individual columns and not rows

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. 

nicksutherland_0-1660842875486.png

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.

nicksutherland_1-1660842989633.png

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.

 

nicksutherland_2-1660843522348.png

 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@nicksutherland,

 

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
DataInsights
Super User
Super User

@nicksutherland,

 

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?





Did I answer your question? Mark my post as a solution!

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

nicksutherland_0-1660932320105.png

 

Expanded to new rows - Project Number is not in this table and as such cannot merge and append

nicksutherland_1-1660932405656.png

 

 

@nicksutherland,

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.