Forum Discussion

JusticeBaird's avatar
JusticeBaird
Frequent Visitor
3 years ago
Solved

Field has List values and type ABC123 values.

I am unable to expand a field/column, which contains [List], null, and type ABC123 records.  I would appreciate a quick custom column / code and/or tips.  Thank you! J

 

 

 

 

  • See this example:

     

     

    let
        Source = List.Zip({{1..10}, {21..30}}) & {1},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = 
            Table.AddColumn(
                #"Converted to Table", 
                "Custom", 
                each 
                if Value.Is([Column1], List.Type) 
                    then Text.Combine(List.Transform([Column1], Text.From), ",")
                else 
                [Column1]
            )
    in
        #"Added Custom"

     

     

    I added a 2nd column to either expand the list or pull the raw value.

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

     

2 Replies

  • edhans's avatar
    edhans
    Community Champion

    See this example:

     

     

    let
        Source = List.Zip({{1..10}, {21..30}}) & {1},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = 
            Table.AddColumn(
                #"Converted to Table", 
                "Custom", 
                each 
                if Value.Is([Column1], List.Type) 
                    then Text.Combine(List.Transform([Column1], Text.From), ",")
                else 
                [Column1]
            )
    in
        #"Added Custom"

     

     

    I added a 2nd column to either expand the list or pull the raw value.

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.