Power Query - Unique list of all columns

Question

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

Answer 1

@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  

Answer 2

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

Answer 3

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