Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
How about:
List.Distinct(List.Union(Table.ToColumns(Tabel1)))
(This is the entire query code)
How about:
List.Distinct(List.Union(Table.ToColumns(Tabel1)))
(This is the entire query code)
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.
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
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?
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |