Dataset issues

[techy_tuner]

Following is the snap shopt of the countries, regions and vendors due to which I have got a lot of duplicate values. 
I want to create different slicers for Countries, regions. 
But the issue which I am facing is the highlighted countries. I want the gulf and the
text inside the bracket should be complete one country. Is it possible ?

[Anonymous]

Hi @techy_tuner ,

I think you may try code as below to transform the region and country table for data hierarchy slicer.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVNNb9swDP0rRE4toALFgO3uuGma5aNZ03bY6h4Ym7WFypQnS0ndX1/KTopihwIDdjFoUaQeH997eBhNgrMNna2s85WCw9/Ghvi31EVh6GyCrVeQrJNULZLbZCnxk9M5yvW7ryM1GpMpdagVLMIL1VsbXKkgfaW8ghtqwtboXMEFcY3uWcEsJ4NcSHHrLWtUGV9qHo5m7pBboN9JChbaVwHjrRX5ilzMtgoE7R47BWvbX894s6eCWICF1rtYOA6mxD5KnUUfg6kjyknBVWBJSfWsdUgmdnE+lGikz42t+9dgY+wOn48RDWd77V8HDIK+7BphZRrME5zMwx61l/oxVg41qx/o0alraabgF9XEp3E4/KMWtEP2cPLduiImF7RFtiylazTUes2CcNMJcqnYYCg0JA638fm7ZHIYEE38v7Jcwlw+0poLy9T2ZC7RYBdDWNEefhMOeNeVNrpppL/wt9FCQWNdfCuuWto4kopb1HuMYG4r1ENdirLRIrZbdhhXqOBek2eUfSeuJBbIkWUJBBdgTVEaGUsx5Oic3hIypEkKWTg///IN7onpNciepUgwCYTUGlv3I07ygIWVJ5b0onMbWSEXZHQXyhAXPrZG98JYo8PhKDEl9Yu+uEmhoNrmTvadgxPpsUgP7FPGqdBkpa11Npe+h6kHGR+B9Wfk+K9j4VbLBNc5DcwE1sLuMbvQ2y4CF4sc607mxB1CJGDiK22bfpfTCiNPKz2gzfgnfSiZ7azrhIjeaWkk0YYoZ0EdiWp6hVJL0DYksuQy41xuiNapPVWbJmoOpuREcELJ3VzBpUMWtc88mm70qD4z+j9b+OhcOBo340+cC59ZFw7Ozfh/Wffdue/G/eDb0ePjGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Regions = _t, Countries = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Regions", type text}, {"Countries", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Regions", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Regions"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Regions", type text}}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Countries", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Countries"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Countries", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Countries", "Countries - Copy"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Countries - Copy", each Text.AfterDelimiter(_, "("), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Countries", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Row ID"}}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Countries", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Countries"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Countries", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type3", {"Row ID"}, {{"Rows", each _, type table [Regions=nullable text, Countries=nullable text, #"Countries - Copy"=text, Row ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Add Index", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Add Index" = Table.ExpandTableColumn(#"Added Custom", "Add Index", {"Regions", "Countries", "Countries - Copy", "Index"}, {"Regions", "Countries", "Countries - Copy", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Add Index", "Max Index by Row ID", each let 
_ID = [Row ID]
in
List.Max( Table.SelectRows(#"Expanded Add Index", each [Row ID] = _ID)[Index])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Countries Result", each if
[Index] = [Max Index by Row ID]
then
[Countries] &""& "(" &""&[#"Countries - Copy"]&""&")"
else
[Countries]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Row ID", "Rows", "Countries", "Countries - Copy", "Index", "Max Index by Row ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Countries Result] <> ""))
in
    #"Filtered Rows"

Original Table:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[techy_tuner]

Hi, 
I appreciate your response. You might have been working on PowerBI From a long time. 
This is a great solution ......But what if we have a pre-defined set of regions. 

[danextian]

This is why it is important to post a workable sample data (not an image) that represents your actual use case and your expected result from it.

[techy_tuner]

https://docs.google.com/spreadsheets/d/1K98B7sga8p6bCVJb9NAoAvyJW5iwmu7KEs1GyqSD1vs/edit?usp=sharing

[Anonymous]

Hi @techy_tuner ,

It seems that your sample link is not public, we don't have permission to download.

Please share us a publick link or you can upload your sample in community directly.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[techy_tuner]

https://docs.google.com/spreadsheets/d/1K98B7sga8p6bCVJb9NAoAvyJW5iwmu7KEs1GyqSD1vs/edit?gid=0#gid=0

[techy_tuner]

Morocco
South Africa & Southern Africa & Indian Ocean
Tunisia & Libya,
East Africa (Kenya and Ethiopia)
👆🏽Expecting this kind of output for creating slicers 

[danextian]

Splitting the text into  several rows is simple but how do you determine which country belongs to a specific region?

[techy_tuner]

I understand your concern sir, my company has provided defined regions for that purpose.
And in Main Table I have created a column defining them. But, I only need to perform transformations on country coulmn. 
So as to get those values on slicer. And this Splitting by comma or ")"seperated isn't working for me. 

[Anonymous · Accepted solution]

Hi @techy_tuner,

According to your statement, I think you only need country column and do some transformation on it to create a slicer.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVNNb9swDP0rRE4toALFgO3uuGma5aNZ03bY6h4Ym7WFypQnS0ndX1/KTopihwIDdjFoUaQeH997eBhNgrMNna2s85WCw9/Ghvi31EVh6GyCrVeQrJNULZLbZCnxk9M5yvW7ryM1GpMpdagVLMIL1VsbXKkgfaW8ghtqwtboXMEFcY3uWcEsJ4NcSHHrLWtUGV9qHo5m7pBboN9JChbaVwHjrRX5ilzMtgoE7R47BWvbX894s6eCWICF1rtYOA6mxD5KnUUfg6kjyknBVWBJSfWsdUgmdnE+lGikz42t+9dgY+wOn48RDWd77V8HDIK+7BphZRrME5zMwx61l/oxVg41qx/o0alraabgF9XEp3E4/KMWtEP2cPLduiImF7RFtiylazTUes2CcNMJcqnYYCg0JA638fm7ZHIYEE38v7Jcwlw+0poLy9T2ZC7RYBdDWNEefhMOeNeVNrpppL/wt9FCQWNdfCuuWto4kopb1HuMYG4r1ENdirLRIrZbdhhXqOBek2eUfSeuJBbIkWUJBBdgTVEaGUsx5Oic3hIypEkKWTg///IN7onpNciepUgwCYTUGlv3I07ygIWVJ5b0onMbWSEXZHQXyhAXPrZG98JYo8PhKDEl9Yu+uEmhoNrmTvadgxPpsUgP7FPGqdBkpa11Npe+h6kHGR+B9Wfk+K9j4VbLBNc5DcwE1sLuMbvQ2y4CF4sc607mxB1CJGDiK22bfpfTCiNPKz2gzfgnfSiZ7azrhIjeaWkk0YYoZ0EdiWp6hVJL0DYksuQy41xuiNapPVWbJmoOpuREcELJ3VzBpUMWtc88mm70qD4z+j9b+OhcOBo340+cC59ZFw7Ozfh/Wffdue/G/eDb0ePjGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Regions = _t, Countries = _t]),
    #"Removed Columns1" = Table.RemoveColumns(Source,{"Regions"}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns1", {{"Countries", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Countries"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Countries", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Countries", "Countries - Copy"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Countries - Copy", each Text.AfterDelimiter(_, "("), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Countries", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Row ID"}}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Countries", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Countries"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Countries", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type3", {"Row ID"}, {{"Rows", each _, type table [Regions=nullable text, Countries=nullable text, #"Countries - Copy"=text, Row ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Add Index", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Add Index" = Table.ExpandTableColumn(#"Added Custom", "Add Index", {"Countries", "Countries - Copy", "Index"}, {"Countries", "Countries - Copy", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Add Index", "Max Index by Row ID", each let 
_ID = [Row ID]
in
List.Max( Table.SelectRows(#"Expanded Add Index", each [Row ID] = _ID)[Index])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Countries Result", each if
[Index] = [Max Index by Row ID] and [#"Countries - Copy"]<> ""
then
[Countries] &""& "(" &""&[#"Countries - Copy"]&""&")"
else
[Countries]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Row ID", "Rows", "Countries", "Countries - Copy", "Index", "Max Index by Row ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Countries Result] <> "")),
    #"Removed Duplicates1" = Table.Distinct(#"Filtered Rows")
in
    #"Removed Duplicates1"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @techy_tuner 

What do you mean by complete one country? Of the many values between the parentheses, which one should be picked? what value do you expect.

Also, pelase provide a workable sample data (not an image) and your expected result. We really don't want to be typing in those texts manually.