Looping (JSON) data from lists in multiple sharepoint sites for Project Online (PWA)

[ao352]

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"

[v-xuding-msft]

Hi @ao352 ,

This blog: https://advisicon.com/power-bi-sharepoint-sites/  may help you.