Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
found this great topic https://community.powerbi.com/t5/Desktop/Custom-RESTful-API-Paging-PowerBI-making-multiple-requests-... which i used to create my own query.
It is almost perfect. but somehow it starts at 500 instead of 1 hope somebody can help me.
the code i have looks like this:
GetPage = (pageId) => let Source = Json.Document(Web.Contents("https://xx.sharepoint.com/_api/search/query?querytext='SPSiteURL:https://xxx.sharepoint.com/sites/xxx+-Filename:.aspx+-RefinableString11:Folder+(RefinableString51:a*+OR+RefinableString51:b*+OR+RefinableString51:c*+OR+RefinableString51:d*+OR+RefinableString51:e*+OR+RefinableString51:f*+OR+RefinableString51:g*+OR+RefinableString51:h*+OR+RefinableString51:i*+OR+RefinableString51:j*+OR+RefinableString51:k*+OR+RefinableString51:l*+OR+RefinableString51:m*+OR+RefinableString51:n*+OR+RefinableString51:o*+OR+RefinableString51:p*+OR+RefinableString51:q*+OR+RefinableString51:r*+OR+RefinableString51:s*+OR+RefinableString51:t*+OR+RefinableString51:u*+OR+RefinableString51:v*+OR+RefinableString51:w*+OR+RefinableString51:x*+OR+RefinableString51:y*+OR+RefinableString51:z*+OR+RefinableString51:1*+OR+RefinableString51:2*+OR+RefinableString51:3*+OR+RefinableString51:4*+OR+RefinableString51:5*+OR+RefinableString51:6*+OR+RefinableString51:7*+OR+RefinableString51:8*+OR+RefinableString51:9*+OR+RefinableString51:0*)'&trimduplicates=false&rowlimit=500&selectproperties='Title%2cRefinableString50%2cRefinableString51%2cRefinableString52%2cRefinableString53%2cRefinableString54%2cRefinableString55%2cRefinableString56%2cRefinableString57%2cRefinableString58%2cRefinableString59%2cRefinableDate10%2cRefinableDate11%2cRefinableString60%2cRefinableString61%2cRefinableString62%2cRefinableString63%2cRefinableString64%2cRefinableString65%2cRefinableString11%2cFilename%2cModifiedBy%2cLastModifiedTime%2cCreatedBy%2cCreated%2cCreatedOWSDATE%2cDocId'&startrow=" & Text.From( pageId*500),[Headers=[#"Accept"="application/json"]])), PrimaryQueryResult = Source[PrimaryQueryResult], RelevantResults = PrimaryQueryResult[RelevantResults], Table = RelevantResults[Table], Rows = Table[Rows], AllRows = List.Transform(Rows, each _[Cells]), RowsToTables = List.Transform(AllRows, each List.Transform(_, each Record.ToTable(_))), SkelToList = List.Transform(RowsToTables, each Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error)), CleanRows = List.Transform(SkelToList, each List.Transform(_[Column1], each Table.PromoteHeaders(Table.RemoveLastN( Table.RemoveColumns( _,{"Name"}), 1) ) ) ), TransposeTable = Table.FromRows(List.Transform(CleanRows, each List.Transform(_, each Record.FieldValues(_{0}){0} ))), ColumnRenames = List.Transform(CleanRows{0}, each { "Column" & Text.From( List.PositionOf(CleanRows{0}, _) + 1), Table.ColumnNames(_){0}}), RenamedTable = Table.RenameColumns(TransposeTable, ColumnRenames) in #"RenamedTable", Pages = {1..NumberOfPages}, #"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each GetPage([Column1])), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Rank", "DocId", "Title", "RefinableString50", "RefinableString51", "RefinableString52", "RefinableString53", "RefinableString54", "RefinableString55", "RefinableString56", "RefinableString57", "RefinableString58", "RefinableString59", "RefinableDate10", "RefinableDate11", "RefinableString60", "RefinableString61", "RefinableString62", "RefinableString63", "RefinableString64", "RefinableString65", "RefinableString11", "Filename", "ModifiedBy", "LastModifiedTime", "CreatedBy", "Created", "CreatedOWSDATE", "PartitionId", "UrlZone", "Culture", "ResultTypeId", "RenderTemplateId"}, {"Custom.Rank", "Custom.DocId", "Custom.Title", "Custom.RefinableString50", "Custom.RefinableString51", "Custom.RefinableString52", "Custom.RefinableString53", "Custom.RefinableString54", "Custom.RefinableString55", "Custom.RefinableString56", "Custom.RefinableString57", "Custom.RefinableString58", "Custom.RefinableString59", "Custom.RefinableDate10", "Custom.RefinableDate11", "Custom.RefinableString60", "Custom.RefinableString61", "Custom.RefinableString62", "Custom.RefinableString63", "Custom.RefinableString64", "Custom.RefinableString65", "Custom.RefinableString11", "Custom.Filename", "Custom.ModifiedBy", "Custom.LastModifiedTime", "Custom.CreatedBy", "Custom.Created", "Custom.CreatedOWSDATE", "Custom.PartitionId", "Custom.UrlZone", "Custom.Culture", "Custom.ResultTypeId", "Custom.RenderTemplateId"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Column1"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.DocId", Order.Ascending}}),
at the end of the url i have a parameter startrow= pageid *500. as it loops it starts at 500,1000 ,1500, 2000, 2500 etc.
it should start at 1 or 0 and then up with 500.
hope somebody can help
thanks in advance.
kr,
Paul
How about the result if you change the end of the url from “startrow= pageid *500” to “startrow= pageid”?
Best Regards,
Herbert
But i need to iterated it found solution for the iteration lets see if that works..
Have you found the solution for the iteration and whether it works?
Best Regards,
Herbert
Hi Herbert,
not yet posted a seperate thread for the itteration but it is not coming together yet.
kr,
Paul
It seems that you've solved your problem in a seperate thread. If possible, you can copy and paste the right solution in this thread.
Best Regards,
Herbert
somehow i have another issue with the part above as i can not schedule it in PowerBi Online.
i guess the problem is this:
GetPage = (pageId) => let
which looks like a function i think this is why i cannot schedule. the strange thing is i thought it was no function and it worked before..
Well hope somebody has a solution/work arround.
kr,
Paul
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |