Parsing JSONs files from a folder

[adrien1804]

Hi Power BI Community,

I'm parsing JSON files from a folder and they could have different architectures on the second layer in the hierarchy for one of the four first items.

I succeeded to make it dynamic, that way when I change sample file it changed the output even column names are different from the the previous folder. However, if the files within this folder have different hierarchy it only display correctly the files similar to the sample file :

From my understanding, the problem is coming from this step :

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

Which is included in this :

#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

To summarize and from my understandings, I need all column names from all JSON files that's way I could expand table column each one of the column listed in those JSON files.

If it's not clear enough, feel very free to ask any questions 🙂

Thank you very much in advance for your help 😀

[adrien1804]

Hi @ImkeF ,
It works perfectly, thank you very much 🙏

[ImkeF · Accepted solution]

Hi @adrien1804 ,
if this needs to be dynamic (at the cost of performance), then you could use the following syntax:

Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(Table.Combine(#"Removed Other Columns1"[Transform File]))),