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

9 Replies

    • Sab's avatar
      Sab
      Helper V
      6 years ago

      Is there any way to extract data, even if it is not in correct JSON format. I am coming up to this point:

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