Expanding list within column

[rmahmood]

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!

[wdx223_Daniel]

Table.SplitColumn(PreviousStepName,"Training Modules",each if _ is list then _ else {_},List.Max(List.Transform(PreviousStepName[Training Modules],each if _ is list then List.Count(_) else 1)))

[mussaenda]

Hi @rmahmood, Is this a custom column or coming from the source? 

[rmahmood]

Hi,

This is actually coming from the source.

[edhans · Accepted solution]

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.

[Greg_Deckler]

@rmahmood Seems like you are going to need an if statement to check if null, then return null or otherwise, extract values from the list like:

#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})

Will be different if you are trying to expand to rows, would be:

#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")

@ImkeF and @edhans might have other thoughts.