cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cgkas Member
Member

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: How to show unique values for each row power query?

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Super User I
Super User I

Re: How to show unique values for each row power query?

@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"

Capture.PNG 

View solution in original post

cgkas Member
Member

Re: How to show unique values for each row power query?

@smpa01 

 

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

View solution in original post

10 REPLIES 10
Super User I
Super User I

Re: How to show unique values for each row power query?

@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 Member
Member

Re: How to show unique values for each row power query?


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

 

 

Super User I
Super User I

Re: How to show unique values for each row power query?

@cgkas  can you give me a sample dataset that is representative of your original data set with desired output

 

cgkas Member
Member

Re: How to show unique values for each row power query?

@smpa01 

Thanks for your help!

 

Below a more representative table.

 

 

IdTypeVsRrCodeTpt1
1A2010PDEFA
1A2466RGHIA
1A5171PABCA
1A5562RXYZXYZ
1A2602PHYWA
1A3633PABCA
1A1853RABCA
1A4620PXYZXYZ
1A2108PABCA
1A9406RXYZXYZ
1A8615RXYZXYZ
2A2295PPOWIA
2A1654PUUROA
2A6331PGHIA
2A1797RABCA
2A8690PGHIA
2A4281RPOWIA
2A7979PABCA
2A1458PABCA
2A1931RGHIA
2A4794RXYZXYZ
2A5981PPOWIA
2A4669RXYZXYZ
1B6122PABCB
1B2010PDEFB
1B2466RGHIB
1B5171PABCB
1B5562RXYZXYZ
1B2602PHYWB
Super User IV
Super User IV

Re: How to show unique values for each row power query?

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

cgkas Member
Member

Re: How to show unique values for each row power query?

@Ashish_Mathur 

 

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:

 

Uniques.jpg

The rest would be the same output, only showing the unique values without "XYZ".

 

Thanks again

Super User IV
Super User IV

Re: How to show unique values for each row power query?

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User I
Super User I

Re: How to show unique values for each row power query?

@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"

Capture.PNG 

View solution in original post

cgkas Member
Member

Re: How to show unique values for each row power query?

@smpa01 

 

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

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors