Forum Discussion

kramaswamy's avatar
kramaswamy
Frequent Visitor
5 years ago
Solved

Expand key-value pair column

Hi all,   Trying to figure out how to expand a column which presents data in a key-value pair format, but where the keys are not defined ahead of time. Here's how the data looks: Ultim...
Need Help
  • kramaswamy's avatar
    kramaswamy
    5 years ago

    Hey Greg and Pat,

     

    Thanks for the insights. I ended up solving it as follows:

     

    GetPositionRatesTable = Table.AddColumn(MyTable, "rates", each Record.ToTable([positionRates])),
    GetPositionRatesTableFiltered = Table.AddColumn(GetPositionRatesTable, "rateKVP", each Table.SelectRows([rates], (r) => Number.From(r[Name]) = Number.From([position_id]))),
    GetPositionRate = Table.AddColumn(GetPositionRatesTableFiltered, "rate", each if Table.RowCount([rateKVP]) >= 1 then [rateKVP]{0} else [Name = "", Value = [hourlyRate]])

  • kramaswamy's avatar
    kramaswamy
    Frequent Visitor
    5 years ago

    Thanks for the insights, Greg - however, Record.Field (and most other methods) all require me to know the names of the columns ahead of time. In the example on MSDN for Record.Field, for example, it says:

    Record.Field([CustomerID = 1, Name = "Bob", Phone = "123-4567"], "CustomerID")

     How do I use it if I don't know the names of the columns?