JSON file on SharePoint Folder

[PBI_Vladimir]

Hi,

I have JSON file in sharepoint folder but when I try to make Dataflow with PQ and when I load file and click on Content column (Binary) in order to load content I got this error message.

 
Any help? Thanks in advance.
Vlada

[ImkeF]

Hi @PBI_Vladimir ,
sorry, missed your previous response.
What I need is a screenshot of the Source-step of the query "Transform Sample File" instead of the one you've posted.
Thx!

[PBI_Vladimir]

Hi ImkeF,
thank you!
With Your answers, You encouraged me to think and research  and so I found a solution (see my reply below)

-----------

let

  Source = Json.Document(Parameter, 65001),

  #"Converted to table" = Table.FromRecords(Source)  

in

  #"Converted to table"

----------------------------

regards
Vlada

[ImkeF]

Hi @PBI_Vladimir ,
please try the following code for that query 

#"Transform Sample file"

let
Source = Json.Document(Parameter, 65001),
#"Converted to table" = Table.FromRows(Source)
in
#"Converted to table"

Otherwise, if that doesn't work, please post a screenshot of the result that the step "Source" in that query returns.

[PBI_Vladimir]

I did it, I put:

let

  Source = Json.Document(Parameter, 65001),

  #"Converted to table" = Table.FromRecords(Source)

in

  #"Converted to table"

-------------

and it's works

[PBI_Vladimir]

If I good understand - this screen shot.

[ImkeF · Accepted solution]

Hi @PBI_Vladimir ,
to debug this error, please go to the query "Transform Sample file" instead and go to the step where the error occurs (usually, you get a buttton at the last step that "jumps" you to the problematic step).
Please paste the M-code for that error here then.

[PBI_Vladimir]

Hi ImkeF,
Thank you for help, here the picture of Transform Sample File ...

and on Show Details here is  a code

---------- Message ----------
Expression.Error: We cannot convert a value of type List to type Record.

---------- Session ID ----------
fb4b7769-0f81-4880-a977-2f1b44caf803

---------- Request ID ----------
89abd794-fa39-4c26-8e81-70141f6fa193

---------- Mashup script ----------
section Section1;
shared Query = let
Source = SharePoint.Files("https://sharepoint........", [ApiVersion = 15]),
#"Filtered rows" = Table.SelectRows(Source, each ([Name] = "ZammadDataFull.json")),
#"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),
#"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),
#"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
#"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),
#"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file")))
in
#"Expanded table column";
shared #"Sample file" = let
Source = SharePoint.Files("https://sharepoint......", [ApiVersion = 15]),
#"Filtered rows" = Table.SelectRows(Source, each [Name] = "ZammadDataFull.json"),
#"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),
Navigation = #"Filtered hidden files"{0}[Content]
in
Navigation;
shared Parameter = let
Parameter = #"Sample file" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type binary, BinaryIdentifier = #"Sample file"]
in
Parameter;
shared #"Transform Sample file" = let
Source = Json.Document(Parameter, 65001),
#"Converted to table" = Record.ToTable(Source)
in
#"Converted to table";
[FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample file""}"]
shared #"Transform file" = (Parameter as binary) => let
Source = Json.Document(Parameter, 65001),
#"Converted to table" = Record.ToTable(Source)
in
#"Converted to table";