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

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

11 Replies

Anonymous

Not applicable

1 year ago

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.

Dataset issues.pbix20 KB

techy_tuner
Helper I
1 year ago
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
  Super User
  1 year ago
  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.

danextian
Super User
1 year ago
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.

Forum Discussion

Dataset issues

11 Replies

Related content

help : dataset refresh issue

Averagex issue

Incremental Refresh Timeout Issue with Large Dataset

Refresh dataset api

Relationship Issue