Remove duplicates from single column power query

Question

From this original table,

I made a second table (using power query).

This second table is to be used for data validation purposes, and I need it to depend on the first table so that any changes will follow through. The problem I'm running into is that my second table is not quite how I want it, I would like to remove any duplicates from each individual column. When I try to remove duplicates in power query, it removes whole rows (which makes sense, I agree), is there a way to remove duplicates from single columns?

Here's the M code I'm using to get from table1 to table2

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Removed Columns" = Table.RemoveColumns(Source,{"Grade", "fb", "fv", "fc", "fcp", "ft", "E", "E05"}),
   #"Grouped Rows" = Table.FromColumns(Table.Group(#"Removed Columns", {"Catégorie"}, {{"Count", each List.InsertRange([Essence],0,List.Distinct([Catégorie]))}})[Count]),
   #"Promoted Headers" = Table.PromoteHeaders(#"Grouped Rows", [PromoteAllScalars=true])
in 
   #"Promoted Headers"

I don't think so .. you will need a separate query for each column — Slai
I agree with @Slai. You need separate queries, because in Power Query each row of data is a record across all available columns. — teylyn
I added the M code i'm using to go from table1 to table2... Seems likes I might be able to get this to work by adding a list.distinct somewhere in there, but I'm not familiar enough with M code to know where. — user2817017
You don't need separate queries, but you likely do need to go beyond the GUI and write some M code. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2018-04-16T02:00:24

If you have a Source table with columns A, B, and C and want to return a table of each column with duplicates removed, then you can write M code like this:

= Table.FromColumns({   
      List.Distinct(Source[A]),
      List.Distinct(Source[B]),
      List.Distinct(Source[C])},
      {"A","B","C"})

More generically (without using explicit column names), you can do it in a few steps like this:

ToColumns = Table.FromList(Table.ToColumns(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RemoveDuplicates = Table.TransformColumns(ToColumns, {{"Column1", each List.Distinct(_)}}),
FromColumns = Table.FromColumns(RemoveDuplicates[Column1], Table.ColumnNames(Source))

Remove duplicates from single column power query

2 Answers