Forum Discussion

rmahmood's avatar
rmahmood
New Member
5 years ago
Solved

Expanding list within column

Hello, I am having trouble trying to get a numerical list to expand or display from within a column in Power Query.  I would appreciate any help!    Please see attached picture.  Thank you!
  • edhans's avatar
    5 years ago

    Try this rmahmood 
    It uses Value.Is to determine if it is a list or not, then extracts the numbers. From there you can split columns, or whatever.

     

    let
        Source = #table(
                    {"ID", "Name", "City", "Other"},
                        {
                            {123, "Alice", "Wonderand", ""},
                            {456, "Bob", "Wonderland", {1..10}}
                        }
                ),
        #"Added Custom" = 
            Table.AddColumn(
                Source, 
                "Custom", 
                each 
                    if Value.Is([Other], type list) 
                    then Text.Combine(List.Transform([Other], Text.From), ",")
                    else [Other]
            )
    in
        #"Added Custom"

     

     

    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.