Extracting value from a lists/ records with multiple levels

[Sharkybu]

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.

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.

[v-jingzhan-msft · Accepted solution]

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])

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!

[Sharkybu]

Thank you, thank you, thank you.

It worked perfectly.

[PwerQueryKees]

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

[Sharkybu]

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]

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

[Sharkybu]

Thank you.