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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Izs
Frequent Visitor

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

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!

2 REPLIES 2
v-stephen-msft
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? 

 

Tq.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors