Column with comma separated values to list

[madluolis]

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
Pedro	| Spain, Italy
Jose	| France, Germany, Italy
Carlos	| Germany, Spain
Ana	| Spain, Portugal, Italy, France, Germany

I need to find a way how to convert this so that each comma separed value in column Country comes to a own row and Name will repeat for each matching case, so like below:

Name	| Country
Maria	| Spain
Maria	| Portugal
Maria	| Italy
Jose	| France
Jose	| Germany
Jose	| Italy
Carlos	| Germany
Carlos	| Spain
Ana	| Spain
Ana	| Portugal
Ana	| Italy
Ana	| France
Ana	| Germany

Thank you for your help!

[pfarias · Accepted solution]

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]

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"