COMMA in Excel text field interpreted during DATA IMPORT

[rudiklein]

Hi,

I have found an issue with comma's in Excel text fields. I have imported a column from an  Excel sheet and transformed it to be distinct (Remove Rows/ Remove Duplicates). However, when I tried to make a relation to another table, I got a message: You can't create a relationship between these two columns, because one of the columns must have unique values. Keep in mind that I imported one column and deduplicated it.

I have created a table visual and exported this visual to CSV. I have imported the previously exported CSV into Power BI and it appeared to have 2 (!) columns. This was caused by comma's in the text fields. An additional column was created containing data from after the comma. The comma itself was no longer in the data. 

By looking at the above steps, one could deduct that the PBI import does something strange here. It shows the imported Excel column as one column, but it in the engine something else happens. The Remove Duplicates seems to apply the distinct over the 2 columns. This makes it possible to have duplicate data in the first/main column. This column is then used to create a relation to another table and fails due to the duplicates.

I'm not sure if this is a known problem, I couldn't find anything similar.

Please let me know if you need a sample of the data.

Bye,

Rudi

[rudiklein]

Hi @v-qiuyu-msft,

This one needs some steps. If you Get Data from the PBI forum case - original table.xlsx and place that in a table. Export the table to "data.csv"(default) and open the data.csv. You will find multiple #NAME errors there. During the testing I noticed this and thought it could be a cause for the wrongly detected duplicates. I now assume that this isn't the case.

However, it is a minor problem, because if you export the data, you will end up with #NAME errors in your output, which could cause issues further on in the process.

Bottomline: for me it is not a direct issues at this time and we can drop it what I'm concerned.

bye,

Rudi

[rudiklein]

Hi @v-qiuyu-msft,

I have found an addtional problem concerning the handling of duplicates. I will upload some images to show it.

This is what happens: if I load a table with duplicates and view the table in the Query View, you will see duplicates (is seems!), however, when you view the same table in the Data View, you will see why the records are NOT duplicates. It appears that one record contains double spaces and the other one doesn't. So they are actually not dups.

This makes you wonder why the double spaces show up as a single space in the record in the Query view and makes you wrongly think you are seeing duplicates.

bye,

Rudi

Query view        Data view