Forum Discussion

Sharkybu's avatar
Sharkybu
Helper II
2 years ago
Solved

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.

 

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

6 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    2 years ago

    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's avatar
      Sharkybu
      Helper II
      2 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Sharkybu
      Helper II
      2 years ago

      Thank you, thank you, thank you.

      It worked perfectly.