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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PaulKeijzers
Frequent Visitor

PowerBi Pro SharePoint Search autorefresh columns to rows

For a Client of mine i am trying to create a dashboard of all documents in a sitecollection.

it was no problem with odata.feed. but the problem with that was that i can not schedule it to refresh.

 

i finally used web.contents. to refresh the data this works but now i have the XML data as it does not want to read the Json of SharePoint i am in a nested table issue.

 

I worked a lot out and came down to this and here is my problem:

Table.FromRows(List.Transform(element1, each List.Transform(_, each Record.FieldValues(_{1}){0} ))),

 

the problem is that record.field values only returns 1 column instead of all my columns. does anybody have a solution so it shows all columns?

 

Furthermore i think i need to add something to overcome the 500 items limit of search but that is a next step.

Hope somebody knows a solution.

 

Underneath the complete code.

 

let
Source = Xml.Tables(Web.Contents("https://tennant.sharepoint.com/_api/search/query?querytext='p144147'&rowsperpage=500&rowlimit=2000&s...'")),
Table = Source{1}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table,{{"QueryId", type text}}),
#"Expanded RelevantResults" = Table.ExpandTableColumn(#"Changed Type", "RelevantResults", {"GroupTemplateId", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "ItemTemplateId", "Properties", "ResultTitle", "ResultTitleUrl", "RowCount", "Table", "TotalRows", "TotalRowsIncludingDuplicates"}, {"RelevantResults.GroupTemplateId", "RelevantResults.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "RelevantResults.ItemTemplateId", "RelevantResults.Properties", "RelevantResults.ResultTitle", "RelevantResults.ResultTitleUrl", "RelevantResults.RowCount", "RelevantResults.Table", "RelevantResults.TotalRows", "RelevantResults.TotalRowsIncludingDuplicates"}),
#"Expanded RelevantResults.Table" = Table.ExpandTableColumn(#"Expanded RelevantResults", "RelevantResults.Table", {"Rows", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"RelevantResults.Table.Rows", "RelevantResults.Table.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),
#"RelevantResults Table Rows" = #"Expanded RelevantResults.Table"{0}[RelevantResults.Table.Rows],
element = #"RelevantResults Table Rows"{0}[element][Cells],
element1 = List.Transform(element, each List.Transform(_[element], each Table.PromoteHeaders(Table.RemoveLastN( Table.RemoveColumns( _,{"Key"}), 1) ) ) ),
element2 = Table.FromRows(List.Transform(element1, each List.Transform(_, each Record.FieldValues(_{1}){0} ))),
Column1 = element2{2}[Column1]
in
Column1

 

I am new to the Powerbi stuff so have tried a lot but syntax incorrect or did not work 😞

 

hope for some answers.

 

Kr,

 

Paul

4 REPLIES 4
v-yuezhe-msft
Employee
Employee


@PaulKeijzers wrote:

For a Client of mine i am trying to create a dashboard of all documents in a sitecollection.

it was no problem with odata.feed. but the problem with that was that i can not schedule it to refresh.

 

Hi @PaulKeijzers,

What is the exact error message when you fail to schedule refresh for the dataset? Please help to post it.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lyda,

 

it is not solved 😞 

the error i get when i use odata.feed = You can't schedule refresh for this dataset because one or more sources currently don't support refresh.

 

that is why i tried web.contents and get stuck in the Nested tables instead of rows.

 

Kr,

 

Paul

Hi Paul,

 

Sorry I cannot help you out on this issue. But I do have a question. Do you have some source for me to find a manual/example howto get data in PowerBI using SharePoint (online) search. I want to query a content type (list item) and use the result in PowerBI. It can be a lot of records and I want to use a autorefresh. Do you have some advise?

 

I did find this: http://makingthings.work/connect-power-bi-to-sp-search/

 

I use this query (for start): https://my tenant/_api/search/query?querytext='ContentTypeId:0x01005CF44F9730394E45A4F413209E88EE9D*'

 

Thanks, Mike

Still no solutions available 😞

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors