none
Parsing JSON using Power Query RRS feed

  • Question

  • Hi,

    I'm a newbie to Power Query, but I have spent a few hours trying to figure out how to get some JSON data into an excel sheet in a useful format.

    I seem to be able to import an individual row or column, but can't seem to get all the columns it import.

    JSON

    [{"Test_Number":"FR6576","deflection_repeat":[{"Time":0,"A":25,"B":25,"C":25,"D":25,"E":25,"F":25,"G":25,"H":25,"I":25},{"Time":15,"A":25,"B":30,"C":35,"D":30,"E":25,"F":25,"G":30,"H":30,"I":25},{"Time":30,"A":25,"B":32,"C":40,"D":32,"E":25,"F":25,"G":32,"H":32,"I":25},{"Time":60,"A":25,"B":32,"C":40,"D":32,"E":25,"F":25,"G":32,"H":32,"I":25},{"Time":240,"A":25,"B":32,"C":40,"D":32,"E":25,"F":25,"G":32,"H":32,"I":25}],"instanceID":"uuid:7dd16945-7ae6-4e3d-974d-f8ed0333b782"}]

     

    Desired output

    "Test_Number","deflection_repeat__Time","deflection_repeat__A","deflection_repeat__B","deflection_repeat__C","deflection_repeat__D","deflection_repeat__E","deflection_repeat__F","deflection_repeat__G","deflection_repeat__H","deflection_repeat__I","instanceID"
    "FR6576","0","25","25","25","25","25","25","25","25","25","uuid:7dd16945-7ae6-4e3d-974d-f8ed0333b782"
    "","15","25","30","35","30","25","25","30","30","25",""
    "","30","25","32","40","32","25","25","32","32","25",""
    "","60","25","32","40","32","25","25","32","32","25",""
    "","240","25","32","40","32","25","25","32","32","25",""

    Any help appreciated, thanks


    • Edited by ghare Thursday, December 7, 2017 10:43 PM
    Thursday, December 7, 2017 9:52 PM

Answers

  • Assuming that the JSON data is in a text file, you can do the following:

    let
        Source = Json.Document(File.Contents(<FilePath>)),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Test_Number", "deflection_repeat", "instanceID"}, {"Test_Number", "deflection_repeat", "instanceID"}),
        ExpandedDeflection_repeat = Table.ExpandListColumn(ExpandedColumn1, "deflection_repeat"),
        ExpandedDeflection_repeat1 = Table.ExpandRecordColumn(ExpandedDeflection_repeat, "deflection_repeat", {"Time", "A", "B", "C", "D", "E", "F", "G", "H", "I"}, {"Time", "A", "B", "C", "D", "E", "F", "G", "H", "I"}),
        ColumnNamesToReplace = List.Skip(Table.ColumnNames(ExpandedDeflection_repeat1)),
        RenamedColumns = List.Transform(ColumnNamesToReplace, each "deflection_repeat__" & _),
        RenamedList = List.Zip({ColumnNamesToReplace, RenamedColumns}),
        RenamedColumnNames = Table.RenameColumns(ExpandedDeflection_repeat1, RenamedList)
    in
        RenamedColumnNames

    Saturday, December 9, 2017 5:01 PM

All replies

  • Assuming that the JSON data is in a text file, you can do the following:

    let
        Source = Json.Document(File.Contents(<FilePath>)),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Test_Number", "deflection_repeat", "instanceID"}, {"Test_Number", "deflection_repeat", "instanceID"}),
        ExpandedDeflection_repeat = Table.ExpandListColumn(ExpandedColumn1, "deflection_repeat"),
        ExpandedDeflection_repeat1 = Table.ExpandRecordColumn(ExpandedDeflection_repeat, "deflection_repeat", {"Time", "A", "B", "C", "D", "E", "F", "G", "H", "I"}, {"Time", "A", "B", "C", "D", "E", "F", "G", "H", "I"}),
        ColumnNamesToReplace = List.Skip(Table.ColumnNames(ExpandedDeflection_repeat1)),
        RenamedColumns = List.Transform(ColumnNamesToReplace, each "deflection_repeat__" & _),
        RenamedList = List.Zip({ColumnNamesToReplace, RenamedColumns}),
        RenamedColumnNames = Table.RenameColumns(ExpandedDeflection_repeat1, RenamedList)
    in
        RenamedColumnNames

    Saturday, December 9, 2017 5:01 PM
  • Imke,

    Thanks for your reply, works great :)  Another question for you.  Is it possible to save the query and run it  in the background when I load another data file or could it be called up in VBA?

    Thanks,

    George

    Sunday, December 10, 2017 10:18 PM