Create new column that has distinct value from 3 other column

[nssidhu]

I have a table that has 3 columns PlaceOfOrigin, PlaOfDelivery & UltimateDestinations. Values in these columns are US States. There are other Columns as well in total 7

How can I create a new Table for a filter that has the list of All the Unique states from these three columns?

[spuder · Accepted solution]

Hi nssidhu,

maybe the UNION in combination with VALUES will help you.

=UNION(VALUES([Origin]),VALUES([Delivery]),VALUES([Destination]))

This should bring you the "virtual" table for further processing. e.g. Filter.

[MarcelBeug]

A Power Query (M) solution:

let
    Source = Input,
    TableWithUniqueValues = 
        Table.FromList(
            List.Distinct(Source[PlaceOfOrigin]&Source[PlaOfDelivery]&Source[UltimateDestinations]),
            null,
            type table[State = text])
in
    TableWithUniqueValues