Advanced JSON Unfolding Question

[fox252]

Hello,

I have logic built into power query (largely thanks to this community) to unfold a number of different JSON files. This works quite well however, there are instances where I have the same column name in different "sections" of a JSON. E.g. The column {marketValueLocal} could exist in both the "positions" section and the "instrument" section which will cause an error and prevent the latter from unfolding (because of the try logic). I would like to prefix every column name with the section it came from; much like the UI based expand function works. E.g. "positions.marketValueLocal" & "instrument.marketValueLocal". Below is a small slice of the code that hopefully helps give context. 

#"Unfold snapshot" = try Table.ExpandRecordColumn(#"Renamed Columns", "snapshot", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Renamed Columns", "snapshot"), (x) => x <> null))))
otherwise #"Renamed Columns",
#"List subjecttype" = try Table.ExpandListColumn(#"Unfold snapshot", "transactions")
otherwise try Table.ExpandListColumn(#"Unfold snapshot", "cash_settlements")
otherwise try Table.ExpandListColumn(#"Unfold snapshot", "disposal_lots")
otherwise try Table.ExpandListColumn(#"Unfold snapshot", "positions")
otherwise try Table.ExpandListColumn(#"Unfold snapshot", "taxlots")
otherwise try Table.ExpandListColumn(#"Unfold snapshot", "instruments")
otherwise try Table.ExpandListColumn(#"Unfold snapshot", "pricing")
otherwise #"Unfold snapshot",
#"Unfold account" = try Table.ExpandRecordColumn(#"List subjecttype", "account", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "account"), (x) => x <> null))))
otherwise #"List subjecttype",
#"Unfold subjecttype" = try Table.ExpandRecordColumn(#"Unfold account", "transactions", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold account", "transactions"), (x) => x <> null))))
otherwise try Table.ExpandRecordColumn(#"Unfold account", "cash_settlements", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold account", "cash_settlements"), (x) => x <> null))))
otherwise try Table.ExpandRecordColumn(#"Unfold account", "disposal_lots", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold account", "disposal_lots"), (x) => x <> null))))
otherwise try Table.ExpandRecordColumn(#"Unfold account", "positions", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold account", "positions"), (x) => x <> null))))
otherwise try Table.ExpandRecordColumn(#"Unfold account", "taxlots", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold account", "taxlots"), (x) => x <> null))))
otherwise try Table.ExpandRecordColumn(#"Unfold account", "instruments", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold account", "instruments"), (x) => x <> null))))
otherwise try Table.ExpandRecordColumn(#"Unfold account", "pricing", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold account", "pricing"), (x) => x <> null))))
otherwise #"Unfold account",
#"Unfold instrument" = try Table.ExpandRecordColumn(#"Unfold subjecttype", "instrument", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold subjecttype", "instrument"), (x) => x <> null))))
otherwise #"Unfold subjecttype",

[fox252 · Accepted solution]

After much pain, I was able to solution. Used the same logic to get the underlying fields dynamically and paired with List.Transform as part of the rename parameter for Table.ExpandRecordColumn. Added as a suffix rather than prefix. Hope it helps someone else save a few hours..

#"Unfold snapshot" = try Table.ExpandRecordColumn(#"Unfold counts", "snapshot.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold counts", "snapshot.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold counts", "snapshot.payload"), (x) => x <> null))), each _ & ".snapshot"))
otherwise #"Unfold counts",
#"Unfold account" = try Table.ExpandRecordColumn(#"Unfold snapshot", "account.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold snapshot", "account.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unfold snapshot", "account.payload"), (x) => x <> null))), each _ & ".account"))
otherwise #"Unfold snapshot",
#"List subjecttype" = try Table.ExpandListColumn(#"Unfold account", "transactions.payload")
otherwise try Table.ExpandListColumn(#"Unfold account", "cash_settlements.payload")
otherwise try Table.ExpandListColumn(#"Unfold account", "disposal_lots.payload")
otherwise try Table.ExpandListColumn(#"Unfold account", "positions.payload")
otherwise try Table.ExpandListColumn(#"Unfold account", "taxlots.payload")
otherwise try Table.ExpandListColumn(#"Unfold account", "instruments.payload")
otherwise try Table.ExpandListColumn(#"Unfold account", "pricing.payload")
otherwise #"Unfold account",
#"Unfold subjecttype" = try Table.ExpandRecordColumn(#"List subjecttype", "transactions.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "transactions.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "transactions.payload"), (x) => x <> null))), each _ & ".transactions"))
otherwise try Table.ExpandRecordColumn(#"List subjecttype", "cash_settlements.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "cash_settlements.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "cash_settlements.payload"), (x) => x <> null))), each _ & ".cash_settlements"))
otherwise try Table.ExpandRecordColumn(#"List subjecttype", "disposal_lots.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "disposal_lots.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "disposal_lots.payload"), (x) => x <> null))), each _ & ".disposal_lots"))
otherwise try Table.ExpandRecordColumn(#"List subjecttype", "positions.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "positions.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "positions.payload"), (x) => x <> null))), each _ & ".positions"))
otherwise try Table.ExpandRecordColumn(#"List subjecttype", "taxlots.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "taxlots.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "taxlots.payload"), (x) => x <> null))), each _ & ".taxlots"))
otherwise try Table.ExpandRecordColumn(#"List subjecttype", "instruments.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "instruments.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "instruments.payload"), (x) => x <> null))), each _ & ".instruments"))
otherwise try Table.ExpandRecordColumn(#"List subjecttype", "pricing.payload", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "pricing.payload"), (x) => x <> null))), List.Transform(Record.FieldNames(Record.Combine(List.Select(Table.Column(#"List subjecttype", "pricing.payload"), (x) => x <> null))), each _ & ".pricing"))
otherwise #"List subjecttype",
#"Renamed instrument" = try Table.RenameColumns(#"Unfold subjecttype",{{List.First(List.Select(Table.ColumnNames(#"Unfold subjecttype"), each Text.StartsWith(_, "instrument."))) as text, "instrument"}})
otherwise #"Unfold subjecttype",

[BA_Pete]

Hi @fox252 ,

When you expand a record field column, or pretty much any nested object, you should get the 'Use original column name as prefix' option at the bottom of the available fields list:

This should do precisely what you're asking for.

Pete

[fox252]

The underlying columns are dynamic; they pop in and out of existence and occasionally change names. Hence the need for the custom expand code rather than the UI function. I am trying to replicate the functionality of the checkbox option you mentioned.