Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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



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 


    Source = Json.Document(Web.Contents("'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}})
    #"Sorted Rows"


 Thanks in advance!

Community Support
Community Support

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.

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? 



Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors