topic Re: How to read JSON when first data is name of columns in DAX Commands and Tips

How to read JSON when first data is name of columns

Nick_2510 — Fri, 20 May 2022 13:58:16 GMT

how to read JSON in power bi when first data is name of columns

{

"fields":

["name_table1", "name_table2", "name_table3"],

"data": [

["d1","d1","d1"],

["d2", "d2", "d2"]

]

}

Re: How to read JSON when first data is name of columns

lbendlin — Sun, 22 May 2022 19:03:06 GMT

Thank you for the nice challenge. Here is one possible approach.

let Source = "{ ""fields"": [""name_table1"", ""name_table2"", ""name_table3""], ""data"": [ [""d1"",""d1"",""d1""], [""d2"", ""d2"", ""d2""] ] }", #"Parsed JSON" = Json.Document(Source), data = #"Parsed JSON"[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}), Ren = List.Zip({Table.ColumnNames(#"Changed Type"),#"Parsed JSON"[fields]}), Res = Table.RenameColumns(#"Changed Type",Ren) in Res

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Re: How to read JSON when first data is name of columns

Nick_2510 — Mon, 23 May 2022 06:47:45 GMT

lbendlin

Hi,

I got an Expression.Error, it must be Text.Type or Binary.Type. But fields=[List], data=[List]

real example - https://drive.google.com/file/d/1OzleprjuacRzFp0cMBlxFESE6ipIJx50/view?usp=sharing

let
Source = Json.Document(File.Contents("C:\Users\voenik\Desktop\ex.json")),
#"Parsed JSON" = Json.Document(Source),
data = #"Parsed JSON"[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
Ren = List.Zip({Table.ColumnNames(#"Changed Type"),#"Parsed JSON"[fields]}),
Res = Table.RenameColumns(#"Changed Type",Ren)
in
Res

Re: How to read JSON when first data is name of columns

Anonymous — Tue, 24 May 2022 12:37:01 GMT

Hi Nick_2510 ,

If there are some null values in the dataset, it will generate an error when trying to parse. You can try to replace the empty value with the empty structure "<div></div>", then it will parse and return the empty value correctly

https://community.powerbi.com/t5/Desktop/The-parameter-is-expected-to-be-of-type-Text-Type-or-Binary-Type/m-p/49345

https://community.powerbi.com/t5/Power-Query/Expression-Error-The-parameter-is-expected-to-be-of-type-Text/td-p/1123176

Best Regards,

Liu Yang

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