Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
madluolis
New Member

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!

 

 

1 ACCEPTED SOLUTION
pfarias
Helper I
Helper I

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

 

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

 

pfarias_0-1657222062314.png

 

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

pfarias_1-1657222227547.png

 

View solution in original post

2 REPLIES 2
pfarias
Helper I
Helper I

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

 

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

 

pfarias_0-1657222062314.png

 

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

pfarias_1-1657222227547.png

 

Vijay_A_Verma
Super User
Super User

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors