Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello to all,
How would be to emulate an SQL query like below for each row in Power Query language?
SELECT DISTINCT(Codes) FROM TABLE
I have the input table with the first 3 columns and I'd like to have a new column (Uniques) that shows the unique "Codes" for each ID and each Type. So the output column would have the values like shown in Column Uniques.
+----+------+------+---------+ | Id | Type | Code | Uniques | +----+------+------+---------+ | 1 | A | X | X,H | +----+------+------+---------+ | 1 | A | H | X,H | +----+------+------+---------+ | 2 | B | F | F,X | +----+------+------+---------+ | 1 | A | H | X,H | +----+------+------+---------+ | 1 | A | X | X,H | +----+------+------+---------+ | 1 | B | F | F | +----+------+------+---------+ | 2 | A | H | F,H,W | +----+------+------+---------+ | 1 | B | F | F | +----+------+------+---------+ | 2 | B | X | F,X | +----+------+------+---------+ | 2 | A | F | F,H,W | +----+------+------+---------+ | 2 | A | W | F,H,W | +----+------+------+---------+ | 1 | B | F | F | +----+------+------+---------+ | 2 | A | H | F,H,W | +----+------+------+---------+
Thanks in advance for any help
Solved! Go to Solution.
Hi,
This M code works
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJLDoMwDETvwrqL2EmceFn6o6tWlRD9qPe/Rt0qoUBMFiESjxnbg1+vBppNs5WDBoxcVzn7w/H38r2ZYEck103OqTsX2EOApN62uxJ7wqS+P57pObMng0nfPYZCb8naij1Eb5O9hh1hHm2lOphYsWdnqNp9JPDqB5gLIPtU4HoZ/vFlDuRd4n1/uxRcxs/xTtMf5YGDMj+O7bGpqB1GSGq1OTFnJZ2xuPNaeCNmm93V4oFdNTrPMY+udid7yas/p/1mB4iz/toZLvZ+gZd7P8fF3i9wbe9bbe9F//4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Type = _t, Vs = _t, Rr = _t, Code = _t, Tpt1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Type", type text}, {"Vs", Int64.Type}, {"Rr", type text}, {"Code", type text}, {"Tpt1", type text}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Id", type text}}, "en-IN"),{"Id", "Type"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"All codes", each Text.Combine(List.Distinct([Code]), ", "), type text}}), Joined = Table.Join(#"Merged Columns", "Merged", #"Grouped Rows", "Merged"), #"Split Column by Delimiter" = Table.SplitColumn(Joined, "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "Id"}, {"Merged.2", "Type"}}) in #"Renamed Columns"
Hope this helps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJLDoMwDETvwrqL2EmceFn6o6tWlRD9qPe/Rt0qoUBMFiESjxnbg1+vBppNs5WDBoxcVzn7w/H38r2ZYEck103OqTsX2EOApN62uxJ7wqS+P57pObMng0nfPYZCb8naij1Eb5O9hh1hHm2lOphYsWdnqNp9JPDqB5gLIPtU4HoZ/vFlDuRd4n1/uxRcxs/xTtMf5YGDMj+O7bGpqB1GSGq1OTFnJZ2xuPNaeCNmm93V4oFdNTrPMY+udid7yas/p/1mB4iz/toZLvZ+gZd7P8fF3i9wbe9bbe9F//4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [d = _t, Type = _t, Vs = _t, Rr = _t, Code = _t, Tpt1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"d", Int64.Type}, {"Type", type text}, {"Vs", Int64.Type}, {"Rr", type text}, {"Code", type text}, {"Tpt1", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Code] <> "XYZ")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"d", "Type", "Code"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"d", "Type"}, {{"ad", each _, type table [d=number, Type=text, Code=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let Source =[ad], List = Source[Code] in List), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"d", "Type"}, #"Extracted Values", {"d", "Type"}, "Extracted Values", JoinKind.LeftOuter), #"Expanded Extracted Values" = Table.ExpandTableColumn(#"Merged Queries", "Extracted Values", {"Custom"}, {"Custom"}), Custom1 = Table.SelectRows(#"Added Index", each ([Code] = "XYZ")), #"Removed Other Columns1" = Table.SelectColumns(Custom1,{"d", "Type", "Vs"}), #"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"), #"Grouped Rows1" = Table.Group(#"Removed Duplicates1", {"d", "Type"}, {{"ad", each _, type table [d=number, Type=text, Vs=number]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each let Source =[ad], List = Source[Vs] in List), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"ad"}), #"Extracted Values1" = Table.TransformColumns(#"Removed Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Merged Queries1" = Table.NestedJoin(Custom1, {"d", "Type"}, #"Extracted Values1", {"d", "Type"}, "Extracted Values1", JoinKind.LeftOuter), #"Expanded Extracted Values1" = Table.ExpandTableColumn(#"Merged Queries1", "Extracted Values1", {"Custom"}, {"Custom"}), Custom2 = #"Expanded Extracted Values"&#"Expanded Extracted Values1", #"Sorted Rows" = Table.Sort(Custom2,{{"Index", Order.Ascending}}), #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns2"
Hello smpa01, it works just perfect. Thanks so much for your kind support.
@cgkas this will work
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4gilWB0EzwPMMwKynIDYDYscpj6ESiMMlahyTnB9MJWo+sLxmxkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Code = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Code", type text}}), #"Removed Duplicates" = Table.Distinct(#"Changed Type"), #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"ID", "Type"}, {{"ad", each _, type table [ID=number, Type=text, Code=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let Source = [ad], List = List.Sort(Source[Code]) in List), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID", "Type"}, #"Extracted Values", {"ID", "Type"}, "Extracted Values", JoinKind.LeftOuter), #"Expanded Extracted Values" = Table.ExpandTableColumn(#"Merged Queries", "Extracted Values", {"Custom"}, {"Custom"}) in #"Expanded Extracted Values"
@smpa01 hello
Thanks for your help. It works just fine, my only issue is that the structure of my actual table has 3 more columns that I didn't think relevant to show in original post to make it sample input simple, but when I add those 3 new columns it doesn't work.
The columns in my actual table are like this:
+----+------+------+------+------+-------+ | Id | Type | Vs | Rr | Code | Tpt1 | +----+------+------+------+------+-------+
And to get the "Uniques" column is still needed only columns "Id", "Type" and "Code". May you help me one more time to fix this issue?
Thanks again.
@cgkas can you give me a sample dataset that is representative of your original data set with desired output
Thanks for your help!
Below a more representative table.
Id | Type | Vs | Rr | Code | Tpt1 |
1 | A | 2010 | P | DEF | A |
1 | A | 2466 | R | GHI | A |
1 | A | 5171 | P | ABC | A |
1 | A | 5562 | R | XYZ | XYZ |
1 | A | 2602 | P | HYW | A |
1 | A | 3633 | P | ABC | A |
1 | A | 1853 | R | ABC | A |
1 | A | 4620 | P | XYZ | XYZ |
1 | A | 2108 | P | ABC | A |
1 | A | 9406 | R | XYZ | XYZ |
1 | A | 8615 | R | XYZ | XYZ |
2 | A | 2295 | P | POWI | A |
2 | A | 1654 | P | UURO | A |
2 | A | 6331 | P | GHI | A |
2 | A | 1797 | R | ABC | A |
2 | A | 8690 | P | GHI | A |
2 | A | 4281 | R | POWI | A |
2 | A | 7979 | P | ABC | A |
2 | A | 1458 | P | ABC | A |
2 | A | 1931 | R | GHI | A |
2 | A | 4794 | R | XYZ | XYZ |
2 | A | 5981 | P | POWI | A |
2 | A | 4669 | R | XYZ | XYZ |
1 | B | 6122 | P | ABC | B |
1 | B | 2010 | P | DEF | B |
1 | B | 2466 | R | GHI | B |
1 | B | 5171 | P | ABC | B |
1 | B | 5562 | R | XYZ | XYZ |
1 | B | 2602 | P | HYW | B |
Hi,
This M code works
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJLDoMwDETvwrqL2EmceFn6o6tWlRD9qPe/Rt0qoUBMFiESjxnbg1+vBppNs5WDBoxcVzn7w/H38r2ZYEck103OqTsX2EOApN62uxJ7wqS+P57pObMng0nfPYZCb8naij1Eb5O9hh1hHm2lOphYsWdnqNp9JPDqB5gLIPtU4HoZ/vFlDuRd4n1/uxRcxs/xTtMf5YGDMj+O7bGpqB1GSGq1OTFnJZ2xuPNaeCNmm93V4oFdNTrPMY+udid7yas/p/1mB4iz/toZLvZ+gZd7P8fF3i9wbe9bbe9F//4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Type = _t, Vs = _t, Rr = _t, Code = _t, Tpt1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Type", type text}, {"Vs", Int64.Type}, {"Rr", type text}, {"Code", type text}, {"Tpt1", type text}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Id", type text}}, "en-IN"),{"Id", "Type"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"All codes", each Text.Combine(List.Distinct([Code]), ", "), type text}}), Joined = Table.Join(#"Merged Columns", "Merged", #"Grouped Rows", "Merged"), #"Split Column by Delimiter" = Table.SplitColumn(Joined, "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "Id"}, {"Merged.2", "Type"}}) in #"Renamed Columns"
Hope this helps.
Hello Ashish, Thanks so much for your help. It works very nice.
I was wondering if you could help me in one more thing if possible, my final goal is to show the unique values in Column "Vs" for each "Id" and "Type" when "Code" = "XYZ", so the output would look like below:
The rest would be the same output, only showing the unique values without "XYZ".
Thanks again
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJLDoMwDETvwrqL2EmceFn6o6tWlRD9qPe/Rt0qoUBMFiESjxnbg1+vBppNs5WDBoxcVzn7w/H38r2ZYEck103OqTsX2EOApN62uxJ7wqS+P57pObMng0nfPYZCb8naij1Eb5O9hh1hHm2lOphYsWdnqNp9JPDqB5gLIPtU4HoZ/vFlDuRd4n1/uxRcxs/xTtMf5YGDMj+O7bGpqB1GSGq1OTFnJZ2xuPNaeCNmm93V4oFdNTrPMY+udid7yas/p/1mB4iz/toZLvZ+gZd7P8fF3i9wbe9bbe9F//4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [d = _t, Type = _t, Vs = _t, Rr = _t, Code = _t, Tpt1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"d", Int64.Type}, {"Type", type text}, {"Vs", Int64.Type}, {"Rr", type text}, {"Code", type text}, {"Tpt1", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Code] <> "XYZ")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"d", "Type", "Code"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"d", "Type"}, {{"ad", each _, type table [d=number, Type=text, Code=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let Source =[ad], List = Source[Code] in List), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"d", "Type"}, #"Extracted Values", {"d", "Type"}, "Extracted Values", JoinKind.LeftOuter), #"Expanded Extracted Values" = Table.ExpandTableColumn(#"Merged Queries", "Extracted Values", {"Custom"}, {"Custom"}), Custom1 = Table.SelectRows(#"Added Index", each ([Code] = "XYZ")), #"Removed Other Columns1" = Table.SelectColumns(Custom1,{"d", "Type", "Vs"}), #"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"), #"Grouped Rows1" = Table.Group(#"Removed Duplicates1", {"d", "Type"}, {{"ad", each _, type table [d=number, Type=text, Vs=number]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each let Source =[ad], List = Source[Vs] in List), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"ad"}), #"Extracted Values1" = Table.TransformColumns(#"Removed Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Merged Queries1" = Table.NestedJoin(Custom1, {"d", "Type"}, #"Extracted Values1", {"d", "Type"}, "Extracted Values1", JoinKind.LeftOuter), #"Expanded Extracted Values1" = Table.ExpandTableColumn(#"Merged Queries1", "Extracted Values1", {"Custom"}, {"Custom"}), Custom2 = #"Expanded Extracted Values"&#"Expanded Extracted Values1", #"Sorted Rows" = Table.Sort(Custom2,{{"Index", Order.Ascending}}), #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns2"
Glad to be of help to you !!!
Hi,
I could not solve it. I thought a simple IF function would work but it did not. Hope someone else can help you. Please keep the question in the same thread so that i can learn from someone else's response.