Forum Discussion

Sab's avatar
Sab
Helper V
6 years ago

Properly load JSON file

Hello,   I need this json file to properly load into Power Query   Details    Thank you so much!
Need Help
camargos88's avatar
camargos88
Community Champion
6 years ago

Hi Sab ,

 

Maybe not the best code, but try it:

 

let
    Source = Json.Document(File.Contents("D:\Downloads\example.json")),
    #"Added Custom" = Json.Document(Source),
    #"Converted to Table" = Record.ToTable(#"Added Custom"),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
    Header = List.Transform(#"Pivoted Column"[column_definitions]{0}, each Record.FieldNames(_){0}),
    Step1 = Table.FromList(List.Transform(#"Pivoted Column"[row_values]{0},
            each
                    Table.Transpose(Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error))
            ), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" =
        Table.Combine({
                Table.Transpose(Table.FromList(Header, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
                Table.ExpandTableColumn(Step1, "Column1", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
        }),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Column1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Database_RecID", Int64.Type}, {"Index_Number", Int64.Type}, {"Support_Type", type text}})
in
    #"Changed Type"

 

 

Sab's avatar
Sab
Helper V
6 years ago

Thanks a lot! I am trying to understand Header and Step1 steps, I guess there is no commands to do those steps and you have to manually write the code?!

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Sab ,

     

    Yes, I thought easier to write the code, it's less steps than UI steps (I think).

    I just navigated through the lists and transformed it to tables.

     

    • Sab's avatar
      Sab
      Helper V
      6 years ago

      Can you do it with UI steps please?

       

      Thanks!

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Sab ,

         

        let
        Source = Json.Document(File.Contents("D:\Downloads\example.json")),
        #"Added Custom" = Json.Document(Source),
        row_values = #"Added Custom"[row_values],
        #"Converted to Table" = Table.FromList(row_values, 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", Int64.Type}, {"Column1.2", Int64.Type}, {"Column1.3", type text}})
        in
        #"Changed Type"

         

        Without the headers.