Forum Discussion
Resolver IHow to return distinct values of a certain column when creating a new table?
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.
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.
2 Replies
- az38
Community Champion
Hi markmess77
for example you can create summarized table like
Table 2 = summarize('Table', 'Table'[CompanyID], "CompanyName", FIRSTNONBLANK('Table'[CompanyName], 1)) 
v-alq-msftCommunity Support
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.
