Forum Discussion

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...

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.

Dataset issues.pbix20 KB

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

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

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.

Dataset issues.pbix20 KB

Forum Discussion

Dataset issues

Related content

help : dataset refresh issue

Averagex issue

Incremental Refresh Timeout Issue with Large Dataset

Refresh dataset api

Relationship Issue