Power BI failed to load only custom column from across multiple SharePoint site

[Izs]

Hallo,

I have more than 5 SharePoint online sites and it continually growing. I use the “Web.Contents” method to call the Sharepoint Search API and then the “Json.Document” method to parse the result. The issue is that I can call and load only the default column but I also need the custom column. How can I do that? Anyone can help?

Here is the M code:

From here I can only load columns other than this custom column,

>>Status, Id, DueDate, Resolution 

let
    Source = Json.Document(Web.Contents("https://e.sharepoint.com/sites/p/_api/search/query?querytext='ParentLink:Issues AND contentclass:STS_ListItem'&filter=hidden,&rowlimit=500&rowsperpage=500&selectproperties='Title,Author,Status,Id,DueDate,Resolution,Priority,Owner,Path,Description,ParentLink,SPWebUrl,UniqueId'&TrimDuplicates=false", [Headers=[Accept="application/json"]])),
PrimaryQueryResult1 = Source[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult1[RelevantResults],
Table = RelevantResults[Table],
#"Converted to Table" = Record.ToTable(Table),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Cells"}, {"Value.Cells"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Value1",{"Value.Cells"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1),
#"Expanded Value.Cells" = Table.ExpandListColumn(#"Added Index", "Value.Cells"),
#"Expanded Value.Cells1" = Table.ExpandRecordColumn(#"Expanded Value.Cells", "Value.Cells", {"Key", "Value", "ValueType"}, {"Value.Cells.Key", "Value.Cells.Value", "Value.Cells.ValueType"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value.Cells1",{"Value.Cells.ValueType"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value.Cells.Key]), "Value.Cells.Key", "Value.Cells.Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","{","",Replacer.ReplaceText,{"UniqueId"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"UniqueId"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value1",{{"SPWebUrl", Order.Descending}})
in
    #"Sorted Rows"

 Thanks in advance!

[v-stephen-msft]

Hi @Izs ,

Before we import the data to Power BI from a data source, we usually use Power query editor to filter the useless data to enable them from loading into the Power BI, you can check these two documents:

Learn How to Filter Records Using Power Query

Filter data (Power Query) - Microsoft Support

You can also use query parameter to filter your table. More details about query parameter, please refer to: Deep Dive into Query Parameters and Power BI Templates | Microsoft Power BI Blog | Microsoft Power B....

If you want to filter data before the data has been imported into PBI, you can click Get Data->advanced options and write some SQL statements to achieve data you want when connecting to SQL Server.

For connecting to SharePoint is currently impossible.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Izs]

Hi Stephen,

Tq for your reply but I'm not trying to connect to the SQL server for some reason. 

I try to solve it by using a "Web.Contents" to get data from across multiple SharePoint sites.

My current code can pull the default column from multiple SharePoint sites but the custom column returns "null". Despite having data. eg: Custom Risk & Issue data in Project Online

How can I do that? 

Tq.