Forum Discussion
Looping (JSON) data from lists in multiple sharepoint sites for Project Online (PWA)
Hi,
Spent a while googling how to pull data from multiple lists in multiple sharepoint sites connected to projects from Project Online.
I couldn't really find a straightforward answer, so with a mix of resources, I've manage to put together the code below. It's working for me so far, but if anyone has a more efficient way that also allows for scheduled refreshes in Power BI service, I'm keen to know.
xxxx - pwa server url e.g. contoso
yyyy - list name e.g. Software
New Column - new column name
lc1 / lc2 - column name as it appears in sharepoint
let PWA_url = "https://xxxx.sharepoint.com/sites/pwa", ProjectsRelativePath = "Projects()?$Filter=ProjectName eq 'One' ", Projects = Json.Document( Web.Contents(PWA_url & "/_api/ProjectData/[en-us]/", [ Headers = [#"Accept" = "application/json"], RelativePath = ProjectsRelativePath ] )), ProjectsList = Projects[value], #"Projects List to Table" = Table.FromList(ProjectsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Projects List to Table", "Column1", { "ProjectId", "ProjectName", "ProjectWorkspaceInternalUrl" }, { "ProjectId", "ProjectName", "ProjectWorkspaceInternalUrl" }), #"Replaced Value" = Table.ReplaceValue(#"Expanded Column1","https://xxxx.sharepoint.com/sites/pwa/","",Replacer.ReplaceText,{"ProjectWorkspaceInternalUrl"}), Final = Table.AddColumn(#"Replaced Value", "New Column", each let PWA_url = "https://xxxx.sharepoint.com/sites/pwa", SharepointPath = [ProjectWorkspaceInternalUrl] & "/_api/Web/Lists/GetByTitle('yyyy')/Items", Items = Json.Document( Web.Contents(PWA_url, [ Headers = [#"Accept" = "application/json"], RelativePath = SharepointPath ] )), ItemsList = Items[value], #"Items List to Table" = Table.FromList(ItemsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Items List to Table", "Column1", { "lc1","lc2"}, {"lc1","lc2"}) in #"Expanded Column1"), #"Expanded New Column" = Table.ExpandTableColumn(Final, "New Column", {"lc1","lc2"}, {"New Column.lc1","New Column.lc2"}) in #"Expanded New Column" |
