Union and then distinct values of a column

[gerard-kouadio]

Hi, 

Here is a description of my problem. 

I have 3 tables, but i will take the example of 2. in each table, I have a common column "common_col". I want to combine the values of this 2 tables to get one column and then return the distinct values : like that i will be sure that i have the values of each tables. 

Script 1 : Kusto

((table1
| project common_col
| distinct common_col)

| union

(table2
| project common_col
| distinct common_col))

| distinct common_col

Script 2  : Kusto

((table1
| project common_col
| distinct common_col)

| union

(mv_pdb_process_stl
| project common_col
| distinct common_col))

| summarize by common_col

after importing the data in Power BI, I tried this :

Nb_distinct_values = DISTINCTCOUNT('Newtable'[common_col])

Nb_Total_rows = COUNTROWS('common_col')

I don't get the same number and it is not normal.

[ryan_mayu]

could you pls provide some sample data and expected output?

[v-aatheeque]

Hi @gerard-kouadio ,

If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @gerard-kouadio ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @gerard-kouadio ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[selimovd]

Hello @gerard-kouadio ,

did you check which records are not matching?

How are the numbers of distinct records and total records?

If I understand you are using Kusto to create the new column?

What exactly do you expect from this post?

Best regards

Denis

[gerard-kouadio]

My data are in ADX and before importing in Power BI to create reports & Dashboard, I did some transformation in ADX (see script in Kusto). After importing in Power BI, I delete also duplicate values in Power Query. 
But, when i link the final table to my fact table, it said there is a many to many relasionship. 
It means there is still duplicate values in my table. Not possible to check one by one because hight volume of data.

That is why I created this 2 DAX measure and noticed that i don't have the same result.

Nb_distinct_values = DISTINCTCOUNT('Newtable'[common_col])

Nb_Total_rows = COUNTROWS('common_col'). 

So I need help mabye to correct my script, or give some idea. thank

[kushanNa · Accepted solution]

Hi @gerard-kouadio 

right click on top of you power query column and select remove duplicates , this will remove any duplicates you are already having