Forum Discussion
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...
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]])
You should be able to use Record.FieldNames() to get the changing names and feed them into Record.Field.
Pat
mahoneypat Yep, was going to say the same thing. Could also use Record.ToList and then use List.First.
