How to show unique values for each row power query?

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

Need Help

Ashish_Mathur

6 years ago

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.

smpa01

6 years ago

cgkas

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"

cgkas
6 years ago
smpa01

Hello smpa01, it works just perfect. Thanks so much for your kind support.

10 Replies

smpa01

Community Champion

6 years ago

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"

cgkas
Helper V
6 years ago
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.
- smpa01
  Community Champion
  6 years ago
  cgkas can you give me a sample dataset that is representative of your original data set with desired output

Forum Discussion

How to show unique values for each row power query?

10 Replies

Related content

Power Query help to extract unique values

Unique Value Power Query

Unique value

Count unique values

Unique values