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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Raaz
Helper II
Helper II

Expanding List Type in Power BI

Hi Experts,

 

In my desktop report,  I have "Sharepoint Online List" connector. One of the columns is of Type "List". When I try to expand it by going through the "Tranform Data", there's no Expand button.

Raaz_0-1610875598312.png

I tried this step, after going through different threads.

Raaz_1-1610875696889.png

and then this step

Raaz_2-1610875757828.pngRaaz_3-1610875815322.png

After clicking "OK", I get this again with no "expand " button.

Raaz_4-1610875897882.png

 

What I am doing wrong?

How can I get a single value from this "List"

 

My List data has values like this (exported my sharepoint list to excel)

ABC;#12

DEF;#45

.....

and I want values before ; i.e ABC, DEF

 

I saw some posts using M code, ut couldn't understand 

 

Please help....

 

Thanks for you help......

 

 

 

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

I've seen that before too.  Just add a custom column with = [ListColumn]{0} to extract the first (single item from each list).

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@Raaz the reason it will not show the arrows to expand because one of the rows doesn't have the value as a list. Having said that the solution @mahoneypat proposed will work if every column has list as a value otherwise it will give an error.

 

To overcome the error I will wrap it around "try otherwise" to catch the error, like this

 

 try [ListColumn]{0} otherwise null

 

 

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mahoneypat
Employee
Employee

I've seen that before too.  Just add a custom column with = [ListColumn]{0} to extract the first (single item from each list).

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks mahoneypat!!!! Your solution worked!!

hi @Raaz, I'm facing exactly the same issue.. could you please elaborate on the below step suggested by @mahoneypat .. I'm still a novice 

Just add a custom column with = [ListColumn]{0}

Thanks  

parry2k
Super User
Super User

@Raaz do you see two arrow on the list column in the header, can you click these arrows and expand the list.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks Parry2k. I don't see the two arrows. I have attached the screenshot above in my post

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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