Getting Unique List in Power Query

Power Query
1

Hello Team,

I have scenario where I need to generate the unique list from the below dummy data in power query, request you to please help me.

Dummy Data.

E-Code Name Department
121 John
132 Jake Finance
121 John HR
555 Chris IT
675 Susan
132 Jake
132 Jake Finance
143 Hari
143 Hari
675 Susan
675 Susan
555 Chris IT

I should get the below as final table.

E-Code Name Department
132 Jake Finance
121 John HR
555 Chris IT
675 Susan
143 Hari
2

Hi @Dharma,

Give this a go. Just paste this M code in a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVNJR8srPyANSSrE6QBFjI5BIYnYqkHLLzEvMS06FSCAr9QgCi5mamgI5zhlFmcVA2jMELGhmDhIMLi1OxG4oAWtMjEHmJxZlwpViiGCxAYsQpuNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-Code" = _t, Name = _t, Department = _t]),
    SortTable = Table.Sort(Source,{{"E-Code", Order.Ascending}, {"Department", Order.Descending}}),
    BufferStep = Table.Buffer( SortTable ),
    RemoveDuplicates = Table.Distinct( BufferStep, {"E-Code", "Name"} )
in
    RemoveDuplicates

I hope this is helpful.

1 Like
3

Hello @Melissa ,

Thank you for the solution…

Quick Question. Is there a way we can get the same solution by making changes using interface, because this will help me to understand the solution in a easy way, So that I can replicate the same in future scenarios.

Thanks in Advance…

4

Hi @Dharma,

Apart from the BufferStep it’s fully UI. Here’s the process:

  1. Sort your table so Departments show before blank/empty department values.
  2. Add a manual step by pressing the fx in front of the formula bar, this returns the previous step name, wrap de function Table.Buffer around it. This loads the table into memory and makes sure your sort order is preserved.
  3. Select both “E-Code” and “Name” columns, right click the header and select remove duplicates.

All done. If you compare the code with the one I’ve shared above the only difference will be the step names.
I hope this is helpful.

1 Like
5

Thank You so much @Melissa I got it … :grinning: :grinning: