"We found an unexpected character in the JSON input" - Sharepoint folder connection

[TenguMan]

I have a Sharepoint connection that I use in a Power BI report that has a new JSON file added to it each week. This week, the report failed to refresh after the new file was added, with an "unexpected character in the JSON input" error. Filtering the folder connection to just the new file returns the following details:

An error occurred in the ‘Transform File’ query. DataFormat.Error: We found an unexpected character in the JSON input.
Details:
Value=P
Position=0

There is no "P" at position 0 (assuming this is referring to the characters in the file) when I open the JSON file. I can add the new file to a blank Power BI report with no issues, and the "Transform File" query that's being used also does not error out when I attempt to refresh "Transform Sample File". The new file does have additional columns that weren't in previous reports, but I am using "ExtraValues.Error" to filter them out as they are not necessary.

What might be causing this, and how can I fix it? Thank you in advance for any assistance!

UPDATE: I have been informed that a scheduled refresh in a different environment for this report has been failing for the past few days, indicating it may be an error with the previous file in the folder. I do not recall having issues with the manual refresh last week, though.

[TenguMan · Accepted solution]

I figured out the problem. There is another folder with "Alert" in the name in the directory, which caused the query to read both of them - the other folder contains .xlsx files that Power BI was attempting to read as .json files...

[Expiscornovus]

Hi @TenguMan,

Can you share a bit more details about your steps or share the Power Query M code?

Are you using the SharePoint.Files and Json.Document functions?

https://learn.microsoft.com/en-us/powerquery-m/sharepoint-files

https://learn.microsoft.com/en-us/powerquery-m/json-document

[TenguMan]

I am using Json.Document for the transform file query and Sharepoint.Files for the resulting table.

Here is an excerpt of the "Transform File" query:

= (Parameter1 as binary) => let
    Source = Json.Document(Parameter1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {[columns of JSON file}),
    [further expansions truncated],
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded properties",{[type transformations here]})
in
    #"Changed Type"

Where Parameter1 points to "Sample File", the query for which is:

= SharePoint.Files("https://YYY.sharepoint.com/teams/TEAMNAME", [ApiVersion = 15])
= Table.SelectRows(Source, each Text.Contains([Folder Path], "MYNAME") and Text.Contains([Folder Path], "Alert"))
= #"Filtered Rows"{0}[Content]

[lbendlin]

Are you sure that is a JSON file and not a ZIP file?

[TenguMan]

Yes. The folder contains only JSON files, no other file types.