Expand key-value pair column

[kramaswamy]

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!

[kramaswamy · Accepted solution]

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

[mahoneypat]

You should be able to use Record.FieldNames() to get the changing names and feed them into Record.Field.

Pat

[Greg_Deckler]

@mahoneypat Yep, was going to say the same thing. Could also use Record.ToList and then use List.First.

[Greg_Deckler]

@kramaswamy Seems like you need Record.Field

Record functions - PowerQuery M | Microsoft Docs

[kramaswamy]

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?