Regions and Countries

Hi techy_tuner -Select the column containing the regions and countries.

Click on Split Column > By Delimiter (choose the delimiter separating the countries, e.g., comma ,).
After splitting, use the Transform tab and choose Unpivot Columns to rearrange the data so each country has its own row.
Identify duplicate rows based on other columns (e.g., Region, Type, Status).
Go to the Home tab in Power Query Editor and select Remove Duplicates.
Click on Close & Apply to load the cleaned and transformed data back into Power BI.

 

 

 

 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pYxBCoNADEWvErIOeIcWcSNaupVZzGjQwGiG6USkp6/2Cu7ef3zeMGBtWRNDp7ksSPjgOIutBK0dvAa1PBM8vzwu8OZkIcpIUH+KbuLP++tvqkb3cq4+8YaObkUJmqjBxyueZfeFL/IyoXM/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Countries = _t, Type = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Countries", type text}, {"Type", type text}, {"Status", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Countries", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Countries.1", "Countries.2", "Countries.3", "Countries.4", "Countries.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Countries.1", type text}, {"Countries.2", type text}, {"Countries.3", type text}, {"Countries.4", type text}, {"Countries.5", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Region", "Type", "Status"}, "Attribute", "Value"),
#"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns", {"Attribute", "Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Country"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Region", "Country", "Type", "Status"})
in
#"Reordered Columns"

 

Hope this helps.