Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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
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 😞