Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cgkas
Helper V
Helper V

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

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 

 

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 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

@smpa01 

 

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

View solution in original post

10 REPLIES 10
smpa01
Super User
Super User

@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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs


@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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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

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/

@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

@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 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@smpa01 

 

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

Glad to be of help to you !!! Smiley Happy

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.