Custom RESTful API Paging - PowerBI making multiple requests to the same page starting at 500???

[PaulKeijzers]

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

[v-haibl-msft]

@PaulKeijzers

How about the result if you change the end of the url from “startrow= pageid *500” to “startrow= pageid”?

Best Regards,

Herbert

[PaulKeijzers]

But i need to iterated it found  solution for the iteration lets see if that works..

[v-haibl-msft]

@PaulKeijzers

Have you found the solution for the iteration and whether it works?

Best Regards,

Herbert

[PaulKeijzers]

Hi Herbert,

not yet posted a seperate thread for the itteration but it is not coming together yet.

kr,

Paul

[v-haibl-msft]

@PaulKeijzers

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

[PaulKeijzers]

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