Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AVH_Tech
Frequent Visitor

Pivot Column results in DataFormat.Error: We reached the end of the buffer...

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"

 

1 ACCEPTED SOLUTION
tarunsingla
Solution Sage
Solution Sage

Try the following: uncheck "Allow data preview to download in nthe backgroup" and "Enable parallel loading of tables" and check if this issue persists or not.

 

Untitled.png

View solution in original post

1 REPLY 1
tarunsingla
Solution Sage
Solution Sage

Try the following: uncheck "Allow data preview to download in nthe backgroup" and "Enable parallel loading of tables" and check if this issue persists or not.

 

Untitled.png

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors