How to return distinct values of a certain column when creating a new table?

[markmess77]

I am creating a new table with DAX with something like the following

New Table =

DISTINCT (

     UNION (

          ALL(Table[CompanyName], Table[CompanyID])

    )

)

I am looking to return a new table based on distinct values of CompanyID. However, the dataset that I am working with has some minor differences in the CompanyName column despite having the same CompanyID. For example, I could have something like

CompanyName	CompanyID
ABC, Inc	123
ABC Inc	123

The result that I want is to only have only one instance of the `123` CompanyID value. It doesn't matter which CompanyName value corresponds with it.

I know I could just have another intermediate table and do something like Another Table = DISTINCT(Table[CompanyID]) and then make connect Another Table with New Table and I would eventually get the result that I want. But ideally I would like to avoid this and simply only return the distinct values of CompanyID in the first table that I created above.

[v-alq-msft · Accepted solution]

Hello, @markmess77

You can do this with Power Query. I created data to play your scenario.

X:

and:

You can go to the "Start" ribbon, click "Add Queries".

You can then use 'Text Filter' to filter 'CompanyName' that contains ','.

result:

Best regards

Allan

If this post helps,then please consider Accepting it as the solution to help the other members find it more quickly.

[az38]

Hi @markmess77 

for example you can create summarized table like

Table 2 = summarize('Table', 'Table'[CompanyID], "CompanyName",  FIRSTNONBLANK('Table'[CompanyName], 1))