PowerBi Pro SharePoint Search autorefresh columns to rows

[PaulKeijzers]

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

[v-yuezhe-msft]

---

@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

[PaulKeijzers]

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

[sp_mike]

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

[PaulKeijzers]

Still no solutions available 😞