Forum Discussion
Helper IIExtract Values From a List of Records
Hi All,
After struggling with this for a couple of days I am giving in and seeking help from the Power BI Gods.... Thanks in advance for any tips or pointers, I truly appreciate it and so does my wife for keeping me sane 😉
I have a column named "types" and each row in the column contains a list which in turn contains multiple records.
The lists in each row have no uniformity. They could contain no records or multiple records. I am trying to extract the "name" from each record in the list and combine them into a new column.
I.e. for each row I will end up with a new column called "ClientTypes" which concatantes all the types. So for the row above it would be VIP, someothertype2, anothertype3.
I have figured out how to extract the name from the first record using the following m query code.
Record.Field([types]{1}?,"name")
What I have I been stuck on for days now is how to work out how many records are in the list for a row and then loop through all those records to extract the names. The above code also results in the following error when the row has a list with no records in it.
Expression.Error: We cannot convert the value null to type Record.
Details:
Value=
Type=[Type]
I feel like I am making this far more complicated then it needs to be and the solution is probably stupidly simple but I just can't seem to figure it out. Thanks in advance for your help and advice. Cheers
Hi FlyKick
I think I made it!
Add a custom column with below code:
let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", ")Attached the complete M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJycgKSlgZKsTrRSkZAppeXF5A0twQLGAOZjo5AwsQMzDcBMr29vYGkkYVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Value = _t]), SourceToList = {Table.ToRecords(Source),Table.ToRecords(Table.RemoveFirstN(Source,1)),{}}, #"Converted to Table" = Table.FromList(SourceToList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "types"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", ")) in #"Added Custom1"Let me know if you have any questions.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
19 Replies
- mahoneypat
Microsoft Employee
You can use an expression like this in a custom column (or custom transform step) to pull the value of the name record from a variable list of records.
= List.Select([types], each Record.FieldNames(_){0} = "name"){0}[name]
Pat
FlyKickHi Pat,
Thanks for taking the time to review my challenge and offer up a solution. Much appreciated. When I tried your suggestion it results in the following error for every row (even ones where the list contains multiple records).
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]If I break down the formula,
= List.Select([types],
This 👆 enumerates the list of records for the specific row we are on.
each Record.FieldNames(_)
This 👆 loops through each of the records in the list. THe underscore _ represents the current record.
{0} = "name"){0}[name]
This 👆 I am a little confused on, {0} gets the first the attribute from the current record in the list which based on my screen shots above I believe that would be the id attribute? It then compares that attribute to the name attribute value? I have no doubt this is wrong but I can't seem to work out the logic 🤷♂️
If the formula did work looking at List.Select it is going to return a list object so I believe we would need to put this inside of a text.combine function so that it aggregates all the values together separated by a comma as a text field. But I can do that once I get the actual list working.
Thanks again for your assistance. Cheers
- mahoneypat
I'm guessing one or more of your Lists is null, so you can wrap that expression with try ... otherwise like this
= try List.Select([types], each Record.FieldNames(_){0} = "name"){0}[name] otherwise null
FYI that Record.FieldNames returns a list with the field names and {0} gets the first one. The {0}[name] at the end gets the first (and only) element of the selected list and the value in the [name] field.
Pat
- FlyKick
Just a quick update, I have managed to get part of the way there with the following expression.
if List.Count([types]) > 0 then List.Select([types], each Record.FieldNames(_){1} = "name"){0}[name] else ""
My only issue now is looping through all the records. The above seems to only return the name of the first record in the list. Records 2 and 3 are ignored.
- FlyKick
Can anyone confim if it is possible to use the each function to iterate to through the list of records and store the value in an array which we can then use a text.combine to store it as a string in the new custom column?
v-jingzhangCommunity Support
Hi FlyKick
I think I made it!
Add a custom column with below code:
let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", ")Attached the complete M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJycgKSlgZKsTrRSkZAppeXF5A0twQLGAOZjo5AwsQMzDcBMr29vYGkkYVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Value = _t]), SourceToList = {Table.ToRecords(Source),Table.ToRecords(Table.RemoveFirstN(Source,1)),{}}, #"Converted to Table" = Table.FromList(SourceToList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "types"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", ")) in #"Added Custom1"Let me know if you have any questions.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
echristophHelper I
This answer gave me an idea:
= Table.TransformColumns(#"YourPreviousStep", {"types", each Text.Combine(List.Transform(_, each Record.Field(_,"Name")), ", "), type text})This worked for me.
Hello,
I have a similar challange I'm trying to solve for as the OP. The code provided in this thread worked really well to pull and combine a string of text values from the each "Record" within the "List".
= Table.AddColumn(#"Changed Type1", "Custom", each let typeList = [Workers], typeCount = List.Count([Workers]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"WorkerName")), ", "))
However, I need to dig into one more record layer. I need to get into the record on "WorkerName" and pull the values for "First" and "Last".
Right now, the results are giving me an error because "Record" in WorkerName is not a text value. I imagine I need to nest a portion of the code above into another segment, but I have no idea where to go from here.
Any help would be appriciated!
