Forum Discussion

Raaz's avatar
Raaz
Icon for Helper II rankHelper II
5 years ago
Solved

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.

I tried this step, after going through different threads.

and then this step

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

 

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......

 

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

    • Raaz's avatar
      Raaz
      Icon for Helper II rankHelper II
      5 years ago

      Thanks mahoneypat!!!! Your solution worked!!

      • parithoshp's avatar
        parithoshp
        New Member
        5 years ago

        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's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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.⚡

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

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

    • Raaz's avatar
      Raaz
      Icon for Helper II rankHelper II
      5 years ago

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