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
