Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

dynamically expand list and records json

Hello everyone, I need your help I have a json as shown in the image. But it's showing as a list, after I click on the list it marks it as "records".
And within "record" there are other "record", I would like to know if there is a way for me to expand all this content available in JSON.
Thanks

JSON WeTransfer File
https://wetransfer.com/downloads/c3fba59fcab9f88b8a9dc2df2a9bda9820220801210058/2cec96

[image1]



[image 2]

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    There is no magic to it - you need to grind through the JSON hierarchy and decide what to flatten and what to ignore.

     

    let
    Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\XML_-_ESTUDIO-1659369620348.json")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "created_at", "info"}, {"_id", "created_at", "info"}),
    #"Expanded info" = Table.ExpandRecordColumn(#"Expanded Column1", "info", {"Ingest"}, {"Ingest"}),
    #"Expanded Ingest" = Table.ExpandRecordColumn(#"Expanded info", "Ingest", {"TakeInfo", "AudioFileNames", "Sequence"}, {"TakeInfo", "AudioFileNames", "Sequence"}),
    #"Expanded TakeInfo" = Table.ExpandRecordColumn(#"Expanded Ingest", "TakeInfo", {"ProductName", "Director", "Operator", "Scene"}, {"ProductName", "Director", "Operator", "Scene"}),
    #"Expanded Scene" = Table.ExpandRecordColumn(#"Expanded TakeInfo", "Scene", {"SceneStart", "SceneNumber", "Episode", "Description", "OperationUnit", "Season", "LastTake", "ValidTake", "Take"}, {"SceneStart", "SceneNumber", "Episode", "Description", "OperationUnit", "Season", "LastTake", "ValidTake", "Take"}),
    #"Expanded SceneStart" = Table.ExpandRecordColumn(#"Expanded Scene", "SceneStart", {"@timestamp", "@timestampMs", "#text"}, {"@timestamp", "@timestampMs", "#text"}),
    #"Expanded Take" = Table.ExpandRecordColumn(#"Expanded SceneStart", "Take", {"TakeNumber", "Feeds", "Slate"}, {"TakeNumber", "Feeds", "Slate"}),
    #"Expanded AudioFileNames" = Table.ExpandRecordColumn(#"Expanded Take", "AudioFileNames", {"AudioFileName"}, {"AudioFileName"}),
    #"Expanded Sequence" = Table.ExpandRecordColumn(#"Expanded AudioFileNames", "Sequence", {"@xmlns:xsd", "@xmlns:xsi", "TapeName", "Title", "Fcm", "ProductName", "Chapter", "Scene", "RecordDate", "CreatedAt", "Framerate", "Events"}, {"@xmlns:xsd", "@xmlns:xsi", "TapeName", "Title", "Fcm", "ProductName.1", "Chapter", "Scene", "RecordDate", "CreatedAt", "Framerate", "Events"})
in
    #"Expanded Sequence"

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    There is no magic to it - you need to grind through the JSON hierarchy and decide what to flatten and what to ignore.

     

    let
    Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\XML_-_ESTUDIO-1659369620348.json")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "created_at", "info"}, {"_id", "created_at", "info"}),
    #"Expanded info" = Table.ExpandRecordColumn(#"Expanded Column1", "info", {"Ingest"}, {"Ingest"}),
    #"Expanded Ingest" = Table.ExpandRecordColumn(#"Expanded info", "Ingest", {"TakeInfo", "AudioFileNames", "Sequence"}, {"TakeInfo", "AudioFileNames", "Sequence"}),
    #"Expanded TakeInfo" = Table.ExpandRecordColumn(#"Expanded Ingest", "TakeInfo", {"ProductName", "Director", "Operator", "Scene"}, {"ProductName", "Director", "Operator", "Scene"}),
    #"Expanded Scene" = Table.ExpandRecordColumn(#"Expanded TakeInfo", "Scene", {"SceneStart", "SceneNumber", "Episode", "Description", "OperationUnit", "Season", "LastTake", "ValidTake", "Take"}, {"SceneStart", "SceneNumber", "Episode", "Description", "OperationUnit", "Season", "LastTake", "ValidTake", "Take"}),
    #"Expanded SceneStart" = Table.ExpandRecordColumn(#"Expanded Scene", "SceneStart", {"@timestamp", "@timestampMs", "#text"}, {"@timestamp", "@timestampMs", "#text"}),
    #"Expanded Take" = Table.ExpandRecordColumn(#"Expanded SceneStart", "Take", {"TakeNumber", "Feeds", "Slate"}, {"TakeNumber", "Feeds", "Slate"}),
    #"Expanded AudioFileNames" = Table.ExpandRecordColumn(#"Expanded Take", "AudioFileNames", {"AudioFileName"}, {"AudioFileName"}),
    #"Expanded Sequence" = Table.ExpandRecordColumn(#"Expanded AudioFileNames", "Sequence", {"@xmlns:xsd", "@xmlns:xsi", "TapeName", "Title", "Fcm", "ProductName", "Chapter", "Scene", "RecordDate", "CreatedAt", "Framerate", "Events"}, {"@xmlns:xsd", "@xmlns:xsi", "TapeName", "Title", "Fcm", "ProductName.1", "Chapter", "Scene", "RecordDate", "CreatedAt", "Framerate", "Events"})
in
    #"Expanded Sequence"
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks a lot lbendlin.


      if there is any other record I have to expand the column in the same way, correct?

      Do you know if there is anything automatic?

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        As I said - there is no magic to it.  You need to manually explain to Power Query which parts of the JSON are important to you.