Forum Discussion

madluolis's avatar
madluolis
New Member
4 years ago
Solved

Column with comma separated values to list

Seeking for kind help in respect a problem which I have, I have next table, which contains two columns, one with comma separated values:   Name | Country Maria | Spain, Portugal, Italy P...
Need Help
  • pfarias's avatar
    pfarias
    4 years ago

    You can add a new column using the Text.Split function as in the example:

     

    =Text.Split([Country], ",")

     

     

    A new column of type List will be created. Then just expand it.

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sykxU0lEKLkjMzNNRCMgvKilNT8zRUfAsScypVIrViVYKSE0pykcoQUh45RenAsXdihLzklN1FNxTi3IT8yqRVTgnFuXkFwPVwOXAhoDlHPNw26ujgGaoUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Country = _t]),
    Custom1 = Table.ReplaceValue(Source,each [Country],each Text.Split([Country],", "),Replacer.ReplaceValue,{"Country"}),
    #"Expanded Country" = Table.ExpandListColumn(Custom1, "Country")
in
    #"Expanded Country"