How to read JSON when first data is name of columns

[Nick_2510]

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

ex

{

    "fields":

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

                          "data": [

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

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

                                       ]

}

[v-yangliu-msft]

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...

https://community.powerbi.com/t5/Power-Query/Expression-Error-The-parameter-is-expected-to-be-of-typ...

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.

[lbendlin]

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

[Nick_2510]

@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