wrong import of data

[Jolyon]

Hello dear community,

I have a question about import of data.

When I try to import the Product-ID from excel, which has a form e.g. 1111.01 or 234.02 and so on (with a point before two last numbers),

Power BI imports it like regular numbers 111101 or 23402 - without point.

I actually need Product ID like a string with a point(as it is in original excel-sheet).

I tried to change the datatype in excel - from standard to text, but it doesn't help.

Is that a bug in the system or can I somehow import it in right way?

thanks a lot!

[Greg_Deckler]

In your query, right-click your column and go to Change Type.

[Jolyon]

Hi @Greg_Deckler
I have already tried it, and it doesn't help, because in this case I lose the point (.) in Product ID.
In the result I should have the Product ID in the same form as in excel, i.e. with point: 1111.01, 12345.02, 1212.04 etc.

[Greg_Deckler]

So that happens if you set it to Decimal Number or Text? I'm not seeing this in my mock-up of your data. If I set it to Decimal Number, it comes through just fine. If I set it to Text, same thing. Are you on the July update of Desktop? I'm wondering if perhaps it is not really a decimal in your data but some kind of extended ascii character that is causing problems? I mention this because the numbers that you posted below seem to be bigger than the normal . character. You can see that if you look closely at the decimal point in your numbers and the period at the end of your sentence.

[Jolyon]

hi @Greg_Deckler
yes, I have the latest July version of BI Desktop.

About "at the decimal point in your numbers and the period at the end of your sentence" - in my previous message I just made it deliberately bold  - to get your attention to the problem of point)

I tried both the integer, decimal and text formats of Product ID in my excel sheet, and after the import - tried to change it in BI Desktop. Nothing helps.

The curios thing is, that some days earlier I have made another report with other datasource (but also  excel and with the same Product ID), and there it was imported correctly - with a point.

I 'll let you know, if I find the cause of the problem.

Regards,

Jolyon

[v-ljerr-msft · Accepted solution]

@Jolyon

I have tested it on my site and was not able to reproduce this issue (I can import the right data without losing the point).

In this scenario, we can still add the point back to the Product-ID column using Query Editor.

First, change type of Product-ID to Text.

Then paste the formula below into Advanced Editor.

    #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Product-ID",Splitter.SplitTextByPositions({0, 2}, true),{"Product-ID.1", "Product-ID.2"}),
    #"Added Suffix" = Table.TransformColumns(#"Split Column by Position", {{"Product-ID.1", each Text.From(_, "en-US") & ".", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Added Suffix",{"Product-ID.1", "Product-ID.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Product-ID")
in
    #"Merged Columns"

Click Done, then you will see the point has already been added.

Regards

[Jolyon]

thanks for the suggestion! I will test it tonight)