Forum Discussion

fox252's avatar
fox252
Frequent Visitor
3 years ago
Solved

Advanced JSON Unfolding Question

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",

Need Help
  • fox252's avatar
    fox252
    3 years ago

    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",

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      fox252
      Frequent Visitor
      3 years ago

      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.

  • fox252's avatar
    fox252
    Frequent Visitor
    3 years ago

    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",