cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lee123
Frequent Visitor

Handling multiple choice sharepoint columns which are not mandatory

Hi All

I'm trying to get some multiple choice sharepoint columns into powerbi and I can't see what the best way to do that is. They turn up in powerquery showing as 'list' but the extract values button does not appear on the column unless I filter out the blanks which I do not want to do as it filters out many otherwise good rows.

 

What shall I do? I'm using the v2 sharepoint connector.

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

It depends on what you want to do with it. For example, I've mocked this up in Excel (ignore that I do have an expand button)

edhans_0-1622689620141.png

You could extract the choices and put in comma delimited list, like so in a Custom Column - use this formula:

 

if Value.Is([List], type list) then Text.Combine([List], ", ")

 

That looks at my List column, and if there is a list in there, combine the values with a comma delimiter.

edhans_1-1622689868152.png

Now you can separate those out with the Transform, Split Column feature.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User III
Super User III

It depends on what you want to do with it. For example, I've mocked this up in Excel (ignore that I do have an expand button)

edhans_0-1622689620141.png

You could extract the choices and put in comma delimited list, like so in a Custom Column - use this formula:

 

if Value.Is([List], type list) then Text.Combine([List], ", ")

 

That looks at my List column, and if there is a list in there, combine the values with a comma delimiter.

edhans_1-1622689868152.png

Now you can separate those out with the Transform, Split Column feature.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

edhans
Super User III
Super User III

@Lee123 - did this help?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Lee123
Frequent Visitor

Thanks- yes right idea, I did need to add else "NotRecorded" to handle the blanks.

edhans
Super User III
Super User III

Great! Glad I was able to help. Can you mark my response as the solution so others know that it works and this thread can be shown to be solved?

Thanks!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors