Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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]])
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
Record functions - PowerQuery M | Microsoft Docs
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.