Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Sharkybu
Helper II
Helper II

Extracting value from a lists/ records with multiple levels

Hello all.

I'm attempting to extract information from a record in a list that is in a list.

The levels can be seen in the attached picture.

keywords ask.jpg

 

My result should be a new list containing just the keyword group and keywords (both [en_us] columns)

Keywords group: Keyword 1

Keywords group: Keyword 2

Keywords group: Keyword 3 and so on

 

I have been using the following formula in a new column to extract the values:

= try List.Transform([Column2][keywordGroups], each (_[name][en_US]) &": "& _[classifications]{0}[term][en_US]) otherwise null

 

This formula only gives me the first value, but I have 11 records in the list.

How do I replace the {0} into something dynamic like the _ used in other places in the formula?

 

Thank you!

 

Editing to further explain the situation.

I have a very messy API that I'm trying to clean unnecessary data from. 

I don't want to use the expend and choose columns option, the api generates about 20 columns that each one is built like this one. In addition, I need this api for multiple reports so I want to create a code that I can copy and paste every time.

I've cleaned most of it but columns like this that have a list inside another list are proving to be challenging.

 I wish I could link the data here but it contains sensitive data.

Thank you for helping.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Sharkybu 

 

I mocked up some data, check if this approach applies. Create a new custom column with below code. 

let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US])

vjingzhanmsft_0-1721099216214.png

 

Full code of the sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "KeywordGroups", each {[logicalName="logical name", name=[en_US="Keyword group"], classifications={[uri="...uri",term=[en_US="keyword 1"]],[uri="...uri",term=[en_US="keyword 2"]],[uri="...uri",term=[en_US="keyword 3"]],[uri="...uri",term=[en_US="keyword 4"]]}]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "extractValues", each let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US]))
in
    #"Added Custom2"

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @Sharkybu 

 

I mocked up some data, check if this approach applies. Create a new custom column with below code. 

let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US])

vjingzhanmsft_0-1721099216214.png

 

Full code of the sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "KeywordGroups", each {[logicalName="logical name", name=[en_US="Keyword group"], classifications={[uri="...uri",term=[en_US="keyword 1"]],[uri="...uri",term=[en_US="keyword 2"]],[uri="...uri",term=[en_US="keyword 3"]],[uri="...uri",term=[en_US="keyword 4"]]}]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "extractValues", each let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US]))
in
    #"Added Custom2"

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Thank you, thank you, thank you.

It worked perfectly.

PwerQueryKees
Super User
Super User

Without the actual data it is very difficult. I'll have a go.

  • Try a Table.FromRecords() whenever you encounter a field with list of records
    = Table.TransformColumns(Source,{{"You Columname Here", Table.FromRecords}})
  • Use the UI: to expand the fields of type table or type record you are interested in

The are definitly ways in M language to make some shortcuts, but undoable without the actual file...

 

Thank you very much. 

Unfortunately it's more complicated, this is one of many columns in a very messy API.

I'm trying to avoid using the expend commend, and just creating a code that i'll be able to copy every time i'll need to make a report. 

m_dekorte
Super User
Super User

How do I replace the {0} into something dynamic like the _ used in other places in the formula?

 

Iterate a list of indicies; for example:

List.Transform( {0..List.Count(yourList)-1}, (x)=> accessList{x} )

 

Or use List.Accumulate to do "something" for each list item

Thank you.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.