Forum Discussion
SharePoint List Data
Hello, I am a new PBI user.
I imported a SharePoint list as a data source to PBI desktop. When I loaded the data I transformed it. All fields are populating as expexted except one gives me an ID rather than the text that is in the SharePoint. I tried extracting that field via transform and still only get IDs.
The text in sharepoint is populated from a list of employee names that are search and selectable. There is another field like this that does not have the same problem.
I am guessing I need to share more info to get help, could you please let me know what that is?
4 Replies
Jbro Screenshots might help. I'm guessing this is either a 'person' type column in sharepoint or maybe a lookup column. You should see a column with the same name (such as "Employee") as the column in SharePoint, but in PowerBI it will have 'record' all the way down and the column heading will have a double arrow next to it. Click the double arrow to expand that column and choose the 'Name' column or whatever info you want.
AllisonKennedythank you for replying!
I have screenshots and more info:
when I select the double arrows it only allows me to extract values.
And there are two columns representing this field
This is what the field/search looks like in SharePoint (there can be one or more employees in this category:
Jbro Great - so this means they can select multiple employees. I would choose the expand to new rows, but depending on what info this table holds and what relationships it has with other tables you may need to reference the query first and do the expand in a new query and create a relationship.
I've done a video on survey data that has similar issues of needing to split one table into fact and dimension tables, so it may give you some context for your data or may just confuse you: https://www.youtube.com/watch?v=UAjAtbN1MAY&lc=UgxgL2VT3OuiI6v8ZVd4AaABAg
Understanding Fact and Dimension table concepts will help you a lot too:
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
