dynamically expand list and records json

[cleytonp]

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]

[lbendlin · Accepted solution]

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"

[cleytonp]

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]

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.

[cleytonp]

sorry @lbendlini just focused on dax.

But it worked perfectly to me, thanks a lot for the help my friend.