I have a data set that is retrieving data from an web/API source that uses pagination. I have a query that calculates the amount of pages based on the initial data that is received. A list ranging from 1 to the total page number is placed in a table and then a function is called with each page number as a value which in turn requests the information from the API per page. The result is a table with all the page numbers in the first column and the related data in the second column. I then extract the data coulmn to display all the fields. This all seems to work fine (however it takes a long time to retrieve all the data from the API).
Issue:
One of the columns includes data in JSON format. I parse the JSON to extract two items (name and value) which creates multiple records/rows for each vlaue in the name field namely id, name and value. At the moment I have about 6000 rows of data being pulled in from the API and there are 11 different values in the label field so this causes the amount of rows to increase 11 x 6000. This is still showing fine (although it takes a long time to retrieve the initial data set). I then remove null from the label field and then pivot the label column to show each label value as a column and the associated value below it. This is where I receive the error on large data sets:
- DataFormat.Error: We reached the end of the buffer...
When I restrict the amount of data I am pulling from the API to a round 500 rows or less it works but when I have a larger data set it fails. This seems to happen on the client side as the data is already reterieved from the source once I apply the pivot.
Below is a code snippet. Is there a limit to how many columns can be pivoted by amount of records (that exceeds the buffer)?
M code:
PagesTotal is the value calculated by another query showing the number of pages (#records/100).
DateFrom is a parameter I enter.
let
numberOfPages = PagesTotal,
Source = {1..numberOfPages},
#"Converted to Table" = Table.Buffer(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "page"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each GetSTdata([page])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Custom.Column1"}),
#"Expanded Custom.Column1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom.Column1", {"id", "customer", "customFieldValueJSon"}),
#"Parsed JSON" = Table.TransformColumns(#"Expanded Custom.Column1",{{"customFieldValueJSon", Json.Document}}),
#"Expanded customFieldValueJSon" = Table.ExpandListColumn(#"Parsed JSON", "customFieldValueJSon"),
#"Expanded customFieldValueJSon1" = Table.ExpandRecordColumn(#"Expanded customFieldValueJSon", "customFieldValueJSon", {"label", "value"}, {"label", "value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded customFieldValueJSon1", each true),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,"Ignore",Replacer.ReplaceValue,{"label"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[label]), "label", "value")
in
#"Pivoted Column"
