We reached end of buffer Error

[bcoulam]

Hello,

I have data I was able to load via the Web API and it displayed fine. When I refreshed the report, I got the "We reached the end of the buffer" error. Here are solutions I have tried:

-Installing the latest version of Power BI (March 2020)

-Checking the size of my pbix file to see if it can be reduced by removing unnecessary columns (149 KB is the size) 

-I tried using the table.buffer function as recommended in another article by inserting the function in the M code where it seemed logical (see screenshot included). I continue to get the buffer error.

Any help here would be appreciated. I have included the full code below.

let
Source = Json.Document(Web.Contents("https://zionsmgtit.my.workfront.com/attask/api/v9.0/project/search?portfolioID_Mod=5e0fb258028800f35... Project=Yes&OR:1:DE:BPCM Project_Mod=in&fields= DE:Current Status&fields=description&fields=owner:name&&fields=condition&fields=DE:Customer Impact L2&fields=plannedStartDate&fields=DE:Employee Impact&fields=DE:Data&fields=DE:Reputation Risk&fields=DE:Process&fields=DE:Schedule Status&fields=DE:L1 Process Primary&fields=DE:L1 Process Secondary&fields=DE:Cyber&fields=DE:Internal Maturity&fields=DE:Legal/Regulatory risk&fields=DE:ITESC Approved&fields=DE:Total Investment/P%26L Impact&fields=DE:Change Initiative&fields=DE:Secondary Strategic Alignment&fields=DE:Primary Strategic Alignment&fields=DE:Risk Rating BCI&method=get&$$LIMIT=2000&apiKey=")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"ID", "name", "objCode", "DE:Current Status", "description", "owner", "condition", "DE:Customer Impact L2", "plannedStartDate", "DE:Employee Impact", "DE:Data", "DE:Reputation Risk", "DE:Process", "DE:Schedule Status", "DE:L1 Process Primary", "DE:L1 Process Secondary", "DE:Cyber", "DE:Internal Maturity", "DE:Legal/Regulatory risk", "DE:ITESC Approved", "DE:Total Investment/P&L Impact", "DE:Change Initiative", "DE:Secondary Strategic Alignment", "DE:Primary Strategic Alignment", "DE:Risk Rating BCI"}, {"Value.ID", "Value.name", "Value.objCode", "Value.DE:Current Status", "Value.description", "Value.owner", "Value.condition", "Value.DE:Customer Impact L2", "Value.plannedStartDate", "Value.DE:Employee Impact", "Value.DE:Data", "Value.DE:Reputation Risk", "Value.DE:Process", "Value.DE:Schedule Status", "Value.DE:L1 Process Primary", "Value.DE:L1 Process Secondary", "Value.DE:Cyber", "Value.DE:Internal Maturity", "Value.DE:Legal/Regulatory risk", "Value.DE:ITESC Approved", "Value.DE:Total Investment/P&L Impact", "Value.DE:Change Initiative", "Value.DE:Secondary Strategic Alignment", "Value.DE:Primary Strategic Alignment", "Value.DE:Risk Rating BCI"}),
#"Sorted Rows" = Table.Sort(#"Expanded Value1",{{"Value.DE:ITESC Approved", Order.Descending}}),
#"Buffer Sorted Table" = Table.Buffer(#"Sorted Rows"),
#"Transform Column" = Table.TransformColumns(#"Buffer Sorted Table", {{"Value.DE:L1 Process Secondary", each if Value.Is(_, type list) then _ else {_} }} ),
#"Extracted Values" = Table.TransformColumns(#"Transform Column", {"Value.DE:L1 Process Secondary", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Value.DE:L1 Process Secondary", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.DE:L1 Process Secondary.1", "Value.DE:L1 Process Secondary.2", "Value.DE:L1 Process Secondary.3", "Value.DE:L1 Process Secondary.4", "Value.DE:L1 Process Secondary.5", "Value.DE:L1 Process Secondary.6", "Value.DE:L1 Process Secondary.7"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.DE:L1 Process Secondary.1", type text}, {"Value.DE:L1 Process Secondary.2", type text}, {"Value.DE:L1 Process Secondary.3", type text}, {"Value.DE:L1 Process Secondary.4", type text}, {"Value.DE:L1 Process Secondary.5", type text}, {"Value.DE:L1 Process Secondary.6", type text}, {"Value.DE:L1 Process Secondary.7", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value.DE:L1 Process Primary", "L1 Process Primary"}, {"Value.DE:L1 Process Secondary.1", "L1 Process Secondary.1"}, {"Value.DE:L1 Process Secondary.2", "L1 Process Secondary.2"}, {"Value.DE:L1 Process Secondary.3", "L1 Process Secondary.3"}, {"Value.DE:L1 Process Secondary.4", "L1 Process Secondary.4"}, {"Value.DE:L1 Process Secondary.5", "L1 Process Secondary.5"}, {"Value.DE:L1 Process Secondary.6", "L1 Process Secondary.6"}, {"Value.DE:L1 Process Secondary.7", "L1 Process Secondary.7"}, {"Value.condition", "Overall Project Status"}, {"Value.DE:Risk Rating BCI", "Risk Rating"}, {"Value.name", "Project Name"}, {"Value.DE:ITESC Approved", "Source"}})
in
#"Renamed Columns"

[bcoulam · Accepted solution]

I figured this was probably a memory issue, so I removed columns I determined were unnecessary and was able to get the API URL to load. Loading so many custom fields through the API really seems to slow it down, but I got the ones I needed.

[edhans]

What step does the error occur in Power Query? The first place I'd be looking is your Text.Combine(List.Transform(_, Text.From), ";") statement. A list returning unexpected amounts, or an unexpectedly large amount of data, could cause an error. You'll need to click on each statement in Power Query and wait for it to resolve or error out to pinpoint the issue.

[bcoulam]

It appears to be happening at the first step, "Source". It happens both when I refresh my current document or if I load the API URL in a new pbix file.

So this is likely a problem with the source I am getting my URL from?

[dax]

Hi @bcoulam , 

I am not sure which factor cause this problem, you could try to remove the table.buffer() function to see whether it work or not. Or you also could try to refer to this post  to see whether there is something wrong when you connect data.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bcoulam]

Hi @dax ,

Thanks for your suggestions. I tried removed the table.buffer function and got the "We cannot reach the buffer" error again. I switched the file origin to 1200:Unicode as stated in the link you sent me. I then get a "We found extra characters at the end of JSON input" error. I get the same result when I add table.buffer back in the code. I've included screenshots here.

Researching this extra characters error now.

[edhans]

Yup. Something is so messed up, your feed cannot even supply the data properly.