Get Full SharePoint List Column Entries

[llhanley]

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!

[llhanley]

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.

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.

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.

[v-yuezhe-msft]

@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

[llhanley]

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]

@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

[llhanley]

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