If Value.Type is list then combine all items otherwise get the actual value?

[olimilo]

With the following sample table, is it possible to check if the value from the [data] column is of type List, then combine all the items into one type Text value, otherwise just get the given value from the [data] column? The value of the List row should all be text values.

data

{List}

1/1/2020

Passed

Basically the output would be (if the {List} contains {"one", "two", "three"}):

data

one, two, three

1/1/2020

Passed

I tried the query below but I'm getting a "can't convert [Data] value to List" error:

= Table.AddColumn(#"Prev Step", "Custom", each if Value.Is(Value.Type([data]), type text) then [data] else List.Combine([data]))

[Icey]

Hi @olimilo ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[edhans · Accepted solution]

See if this returns what you need @olimilo 

let
    Source = Table.FromList(
    {
        [Data = 1],
        [Data = {1,2,3}],
        [Data = #date(2020,1,1)]
    },
    Record.FieldValues,
    {"Data"}
),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "Custom", 
            each 
            if Value.Is([Data], type list) = true
            then Text.Combine(List.Transform([Data], Text.From), ", ")
            else null
        )
in
    #"Added Custom"

Here my list is numbers, but Text.From converts to text for the concatenation.

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.

You can replace the "else null" part of the function above to "else [Data] to pull the Data column value.

[Jimmy801]

Hello @olimilo 

to the perfect answer of @edhans i just want to show an example how to do it without adding a column, but with a transform of the column instead. @edhans I used your code as a basic if you don't mind 🙂

let
    Source = Table.FromList(
    {
        [Data = 1],
        [Data = {1,2,3}],
        [Data = #date(2020,1,1)]
    },
    Record.FieldValues,
    {"Data"}
),
    Trans = 
        Table.TransformColumns(
            Source, 
            {
                {
                    "Data", 
                    each 
                    if Value.Is(_, type list) = true
                    then Text.Combine(List.Transform(_, Text.From), ", ")
                    else _
                }
            }
        )
in
    Trans

BR

JImmy

[olimilo]

Hi @Jimmy801 & @edhans,

Both solutions worked perfectly for me! Thank you very much for the help!

[Jimmy801]

Hello @olimilo 

then it would be great if you would mark both answers as solution

BR

Jimmy

[Anonymous]

even if @mumbaicharaja  hasn't promoted the answer to solution yet, you used the syntax used here, for a structure that contains nested tables

if Value.Is(c{0}, type table) 

 and I think is correct.

The problem instead lies in the argument of List.Combine function

which expect  a list of list

List.Combine({{1, 2}, {3, 4}})

try using Text.Combine([Data],",")

let
    Source = Table.FromRecords({[col={"111","222","333"}],[col="aaa"],[col="bbb"]}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Value.Is([col], type list)  then Text.Combine([col],",") else [col])
in
    #"Added Custom"

[ziying35]

@olimilo 

= Table.AddColumn(#"Prev Step", "Custom", each if [data] is list then List.Combine([data]) else [data])

[olimilo]

Hi@ziying35 

Thanks however I'm getting this error for all items under the [data] column: We cannot convert the value "xyz" to type List. The "xyz" there is the first value that appears on the list. The same error is raised even if there's only one item in the list.

The list item in the screenshot would have the values: {"Indonesia", "Malaysia"}.

[ziying35]

@olimilo 

I'm sorry, I made a mistake in the previous code. Try this:

= Table.AddColumn(#"Prev Step", "Custom", each if [data] is list then Text.Combine([data], ",") else [data])

[Greg_Deckler]

@olimilo - Maybe using Type.Is and List.Accumulate but someone like @ImkeF or @edhans would know better.