Power Query - Unique list of all columns

Reuven Leibowitz 41 Reputation points
2020-11-23T16:17:35.993+00:00

Hi

I need to create unique list for each column in my table.

Attached is what I'm trying to do:

let
Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
Custom1 = Table.FromList( Table.ColumnNames(Source)),
Custom2 = Table.AddColumn(Custom1, "UniqueLists", each List.Distinct(Source [ _ ] ))
in
Custom2

Custom1 returns a table of one column containing all column names. I have to use it to refer to each column

But how should I write it?

My Misunderstanding is using each and _

Thank you

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,801 questions
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2020-11-23T18:19:19.21+00:00

    @Reuven Leibowitz

    Check the following demo, from what I understood that should do it:

    let  
        Source = Table.FromRecords(  
            {  
                [Product="A", City="Seattle", Seller="Seller1"],  
                [Product="A", City="London", Seller="Seller2"],  
                [Product="B", City="London", Seller="Seller1"],  
                [Product="C", City="Berlin", Seller="Seller1"],  
                [Product="C", City="Berlin", Seller="Seller3"]  
            },  
            type table [Product=text, City=text, Seller=text]  
        ),  
        OtherTable = Table.FromList(  
            Table.ColumnNames(Source), Splitter.SplitByNothing(),  
            type table [ColumName=text]  
        ),  
        DistinctLists = Table.AddColumn(OtherTable, "DistinctLists", each  
            List.Distinct(  
                Table.Column(Source, [ColumName])  
            ),  
            type list  
        )  
    in  
        DistinctLists  
    
    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Reuven Leibowitz 41 Reputation points
    2020-11-23T23:08:54.013+00:00

    I appreciate your effort. you absolutely solved my problem.
    Thank you very much


  2. Lz._ 8,991 Reputation points
    2020-11-24T10:12:44.62+00:00

    For the record, another way:

    let
         Source = Table.FromRecords(
             {
                 [Product="A", City="Seattle", Seller="Seller1"],
                 [Product="A", City="London", Seller="Seller2"],
                 [Product="B", City="London", Seller="Seller1"],
                 [Product="C", City="Berlin", Seller="Seller1"],
                 [Product="C", City="Berlin", Seller="Seller3"]
             },
             type table [Product=text, City=text, Seller=text]
         ),
        OtherTable = Table.FromRows(
            List.Transform(Table.ColumnNames(Source), each
                {_, List.Distinct(Table.Column(Source, _))}
            ),
            type table [ColumName=text, DistinctLists=list]
        )
    in
        OtherTable
    
    0 comments No comments