Forum Discussion
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:
Ultimately, what I need to do is a lookup against that column, where I'm matching a value from the table against the first "column" in that expanded record.
Any insights would be appreciated. Thanks!
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]])
5 Replies
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.
kramaswamy Seems like you need Record.Field
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?
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]])
