topic Re: JSON file on SharePoint Folder in Power Query

JSON file on SharePoint Folder

PBI_Vladimir — Wed, 11 May 2022 16:07:03 GMT

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

Re: JSON file on SharePoint Folder

ImkeF — Thu, 12 May 2022 07:28:53 GMT

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.

Re: JSON file on SharePoint Folder

PBI_Vladimir — Thu, 12 May 2022 09:42:30 GMT

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";

Re: JSON file on SharePoint Folder

ImkeF — Thu, 12 May 2022 13:15:34 GMT

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.

Re: JSON file on SharePoint Folder

PBI_Vladimir — Mon, 16 May 2022 09:05:16 GMT

If I good understand - this screen shot.

Re: JSON file on SharePoint Folder

PBI_Vladimir — Thu, 19 May 2022 09:11:43 GMT

I did it, I put:

let

Source = Json.Document(Parameter, 65001),

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

#"Converted to table"

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

and it's works

Re: JSON file on SharePoint Folder

ImkeF — Thu, 19 May 2022 18:45:17 GMT

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!

Re: JSON file on SharePoint Folder

PBI_Vladimir — Thu, 19 May 2022 19:25:23 GMT

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)

#"Converted to table"

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

regards
Vlada