Dataset issues

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.