Reply
Sharkybu
Helper II
Helper II
Partially syndicated - Outbound

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
v-jingzhan-msft
Community Support
Community Support

Syndicated - Outbound

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
v-jingzhan-msft
Community Support
Community Support

Syndicated - Outbound

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!

Syndicated - Outbound

Thank you, thank you, thank you.

It worked perfectly.

PwerQueryKees
Super User
Super User

Syndicated - Outbound

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

 

Syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

Thank you.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)