Forum Discussion

Jolyon's avatar
Jolyon
Icon for Helper III rankHelper 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 numb...
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

Jolyon's avatar
Jolyon
Icon for Helper III rankHelper III
10 years ago

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's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

  • Jolyon's avatar
    Jolyon
    Icon for Helper III rankHelper III
    10 years ago

    thanks for the suggestion! I will test it tonight)