Forum Discussion
Use Table.ExpandRecordColumn to convert SharePoint Author to Name?
I want to create a summary count of who created / modified items in a SharePoint list. I can see AuthorID and EditorID but how can I translate to a name? I found this Solved: How to display name of Created By from Sharepoint ... - Microsoft Power BI Community but not sure if the UI has changed since then since I can't find similar option.
I also found this powerbi - Ho to Change Author ID from SharePoint list to names in Power BI? - SharePoint Stack Exchange but again the UI has changed so not sure how to do it now.
I beat my head against this enough and figured it out. If anyone else needs this in the future.
1. Go into Transform data
2. Find the field reference as a record by scrolling far over on right of columns
3. Click the double arrow
4. Select the record fields you want, such as Title for full name.
5. Apply changes
Now you will have a new column available called Author.Title or Editor.Title.
The advanced editor adds these lines.
#"Expanded Author" = Table.ExpandRecordColumn(#"PREVIOUS LINE ENTRY", "Author", {"Title"}, {"Author.Title"}), #"Expanded Editor" = Table.ExpandRecordColumn(#"Expanded Author", "Editor", {"Title"}, {"Editor.Title"})
2 Replies
I beat my head against this enough and figured it out. If anyone else needs this in the future.
1. Go into Transform data
2. Find the field reference as a record by scrolling far over on right of columns
3. Click the double arrow
4. Select the record fields you want, such as Title for full name.
5. Apply changes
Now you will have a new column available called Author.Title or Editor.Title.
The advanced editor adds these lines.
#"Expanded Author" = Table.ExpandRecordColumn(#"PREVIOUS LINE ENTRY", "Author", {"Title"}, {"Author.Title"}), #"Expanded Editor" = Table.ExpandRecordColumn(#"Expanded Author", "Editor", {"Title"}, {"Editor.Title"})thanks a million!
