Forum Discussion
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 |
| 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!
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.
2 Replies
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"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.
