Forum Discussion

techy_tuner's avatar
techy_tuner
Helper I
1 year ago
Solved

Dataset issues

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...
Data Protection
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

techy_tuner's avatar
techy_tuner
Helper I
1 year ago

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

  • danextian's avatar
    danextian
    Super User
    1 year ago

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

     

     

  • techy_tuner's avatar
    techy_tuner
    Helper I
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.