Power Query merge distinct lists into table

0
votes

I have a excel table with data in multiple columns. I am trying to create another table that will be used for in-cell dropdown with only unique values. I need to get unique values independently between each columns.

Source table

geo   product   type
g1    p1        t1
g1    p2        t1
g1    p3        t2
g1    p4        t1
g2    p1        t2
g2    p2        t1
g2    p3        t2
g2    p4        t1

Expected result:

geo   product   type
g1    p1        t1
g2    p2        t2
null  p3        null
null  p4        null

I can get unique columns as lists

geo_list = List.Distinct(Table.Column(Source, "geo"))
product_list = List.Distinct(Table.Column(Source, "product"))
type_list = List.Distinct(Table.Column(Source, "type"))

But cannot find a way how to merge it into one table without any kind of join.

2
excelpowerquerym

2 Answers

1
votes

Similar to this post, you can use Table.FromColumns to assemble a table from your lists.

Table.FromColumns(
    {
        List.Distinct(Source[geo]),
        List.Distinct(Source[product]),
        List.Distinct(Source[type])
    },
    {"geo","product","type"}
)
0
votes

With Office 365 you don't even need Power Query for that, it's much easier with UNIQUE:

=UNIQUE(Table1[geo])

enter image description here

But cannot find a way how to merge it into one table without any kind of join.

I think you should not try to put them into one table, but keep as separate. The idea behind tables is that each row contains information related to each other which is apparently not the case for your data.