Forum Discussion
PowerBI reading column with multiple values as Table
I'm trying to create a dashboard from a SharePoint list. The list contains few columns with multiple names. The problem is when I try to read the same in PowerBI, these columns are being read as table format and when I try to expand, it shows there is no data though there is data present. In a similar style, if a column contains only one name, then I was able to extract the names out of it but it doesn't work the same way if there are more than one name. Did anyone face similar issue and were you able to solve it, if so, how?
Sample data:
ColA ColB ColC
1 John Doe John Doe, Tim Apple
2 Tim Apple Tim Apple, Steve Cook
3 Steve Cook Steve Cook
From the above, I was able to work successfully on ColB but ColC doesn't return any value.
I was able to figure this out.
There is a column which is retreived along with other columns during this process named "FieldValuesAsText". Using this column, we can convert more than one column with data as lists, tables etc and extract the data values at the same time.
This worked flawlessly and it retreived the data in shorter time as well.
4 Replies
I was able to figure this out.
There is a column which is retreived along with other columns during this process named "FieldValuesAsText". Using this column, we can convert more than one column with data as lists, tables etc and extract the data values at the same time.
This worked flawlessly and it retreived the data in shorter time as well.Anonymous
Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.
This solution looks very simple and effective. Which lead me to another question - Why did PowerBI confuse us by simply not showing us only this columns. Afterall, most of the users who use SharePoint as the data-source would first want to see the direct columns from the SharePoint list. What might be the reason PowerBI did what it did?
Many thanks to you, saving my sleep. It was really tough figuring out on my own. Thanks for posting your suggestion.
