Forum Discussion
Helper IIExtract Values From a List of Records
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.
Microsoft EmployeeYou 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
Helper IIHi 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
Hi Pat,
So is it possible to get all of the names and then return a list that we can then convert to text using text.combine? I am trying to extract all the clients types not just the first one. So this works if the client has just one tag but if they have many it only gets me the first.