cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dkay84_PowerBI New Contributor
New Contributor

Power Query Challenge for you M Ninjas

I would like a query or function that will append every column in a table into a single list with dups removed.  The names and number of columns is random so the solution needs to be dynamic.

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Power Query Challenge for you M Ninjas

How about:

List.Distinct(List.Union(Table.ToColumns(Tabel1)))

(This is the entire query code) 

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
Steve_Wheeler Established Member
Established Member

Re: Power Query Challenge for you M Ninjas

Will a single-column table result do? - e.g.:

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"
MarcelBeug Super Contributor
Super Contributor

Re: Power Query Challenge for you M Ninjas

How about:

List.Distinct(List.Union(Table.ToColumns(Tabel1)))

(This is the entire query code) 

Specializing in Power Query Formula Language (M)

View solution in original post

alecpeterson Frequent Visitor
Frequent Visitor

Re: Power Query Challenge for you M Ninjas

This is pretty awesome.  Is it possible to transpose the list so that the column headers remain and we still get distinct values for each column?

alecpeterson Frequent Visitor
Frequent Visitor

Re: Power Query Challenge for you M Ninjas

This is pretty awesome.  Is it possible to transpose the list so that column headers remain and we still get distinct values for each column?  Kind of like a transposed version of:          List.Distinct(Table.ToColumns(Tabel1))              except only unique values.

 

Thank you sir,

Alec

 

Highlighted
neurojelly New Member
New Member

Re: Power Query Challenge for you M Ninjas

Hi Marcel,

 

Could you also do?:

List.Distinct(Table.Schema(Table1)[Name])

 

Question about List.Union:

I understand that "The returned list contains all items in any input lists" and the Union is operating on the output of Table.ToColumns which returns a nested list of "columns of values". However since our values are all coming from the same table can't we just grab the table columns directly as in my first question above?

 

Closeing statement:

Almost every time I visit this site your solution is typically chosen as the answer. I understand what it would take to accomplish this and its impressive. Really great work. Thanks for all your posts clarifying the use of the M language on this forum.

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 248 members 2,425 guests
Please welcome our newest community members: