Extract Values From a List of Records

[FlyKick]

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 

[earthworm8]

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!

[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-jingzhang · Accepted solution]

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.

[echristoph]

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.

[andremoon]

This is the right solution. I implemented it on a similar case and it works perfectly.

[dkuldip]

Amazing...saved lot of time for me in a similiar issue. 

[FlyKick]

@jim you are a true genius! Thank you this has worked perfectly! Much appreciated. Out of interest what is the best practice way to handle this situation? Is extracting the values into a comma seperated list ok or is it recommended to extract them to a seperate table and create a relationship? 

[v-jingzhang]

@FlyKick Thank you! I also struggled two days to work it out 😁

In this situation, I'm not sure what will be the best practise. It's up to you!

At present with Text.Combine, it converts a list of names into a one-line string, which will not add new rows to the table.

Another common practice is to return a list of names in the new column (removing Text.Combine part can achieve this). Then expand the list column to new rows. This will add multiple new rows into the table with other column values copied. This may make it easier when you want to do aggregate calculation per name. 

Extracting them to a separate table is also ok. For example, you can remove duplicated values and use the separate table as a Dim Table. Then connect the Dim table to other Fact tables. Create a model like a star schema. 

If you are going to have multiple tables in the model, creating a star schema model may be a good choice. See

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Best Regards,
Jing

[FlyKick]

Jing,

WOW I didn't realise how much time you invested to help me out. Truly appreciate it, thats well above and beyond! Thanks for clarifying their is no best practice I thought that might be the case. 

Makes sense as their are so many different variables that could impact the "best" solution in this case. Thanks again for all your help. Cheers 

[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. 

[mahoneypat]

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

[FlyKick]

Hi 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.