Forum Discussion

Jolyon's avatar
Jolyon
Helper III
10 years ago
Solved

wrong import of data

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!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    10 years ago

    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

6 Replies

    • Jolyon's avatar
      Jolyon
      Helper III
      10 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        10 years ago

        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.