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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
llhanley
Regular Visitor

Get Full SharePoint List Column Entries

Hi all,

I have a SharePoint choice column I want to use for reporting. I need to count actual vs possible for that column. How do I get the values for the possible choices through Power BI without creating a separate column/table that won't update with the SharePoint column in Power BI.

 

Example: Let's say we're doing research and counting animals at Yellowstone. I have a choice column named "Animals." The choices are currently 26 animals from Apes to Zebras. The list data contains say, 15 of those animals in some combination. I want to do a couple of things with this information. First, I'd like to group the animals in the list of possibles - hairy mammals vs unhairy mammals, or related to dogs vs related to cats and then show a percentage of the animals recorded to those categories. I also need to show the number of animals reported to the number of animals possible.

 

How do I get the full list of animals in the column so that I can do the reporting that is connected to the SharePoint column choices? II know I need to create a new table for the broder categories (dogs, cats, etc.). I just don't want to re-create the Animals column separately in Power BI. don't see how to get anything other than the data reported through the list content.  

 

Thanks in advance!

5 REPLIES 5
llhanley
Regular Visitor

Here's what I've got.

 

1. A SharePoint list with 26 kinds of animals in it. This list could be updated to include new animals. I want to be able to get the values in this list live so that I'm not creating another table in Power BI that has to be updated separately from the SharePoint List it originated from.

AnimalsColumn.png

 

 

2. List data that doesn't use all 26 of the values in the list. It may someday, but for now, it doesn't. I want to report on this data in relation to the full list of animals and some sub-grouping items that I note in #3 below.

Animalssheet.png

 

3. I want to do some sub-grouping of the animals by size and type, such as in the table below. I don't want to create this lookup in SharePoint because they don't play nicely with much, and I don't need the rangers to be populating this data anyway. I just need it to report from behind the scenes, so I want to create it from inside Power BI.

 

AnimalInfo.png

@llhanley,

"I want to be able to get the values in this list live "

When you add new values in the choice column, you can click "Refresh" in Power BI Desktop to bring new values.

" I want to do some sub-grouping of the animals by size and type, such as in the table below. I don't want to create this lookup in SharePoint because they don't play nicely with much"

How about you create a relationship using the Animal field between your orginal table and the group table?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I need all of the values that could be chosen in the Animals column in SharePoint. What I am getting in Power BI is just the data that is intered into the list, not what is behind the list. 

 

I don't want to create a separate table of Animals in Power BI that has to be edited every time someone updates the column choices in SharePoint.

 

e.g., Someone adds Bison, Elk, Lynx, Frog and Fish s to the Animals column. If I don't have a way to capture those changes programmatically in Power BI, my correlation table will break because those values have to be added in Power BI manually. I'd like to eliminate as much manual data entry as possible.

v-yuezhe-msft
Employee
Employee

@llhanley,

Please expand the choice column in Power BI Desktop following the guide in the blog below, then calculate your expected count values based on the data.

https://whitepages.unlimitedviz.com/2018/01/using-power-bi-to-report-on-multi-value-sharepoint-field...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have a different issue. I'll put a new message together that better describes what I am looking for. Thank you!

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors