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

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]])