Numerical Format in Excel not imported into Power BI

[Anonymous]

I have a few Excel files with similar content but was created for different region. I formatted my Excel files similarly with one of the columns needing the format to be Number. When I tried to import my tables into Power BI, one import was correctly imported but all other 3 imported the same column as text. I tried to make sure that the Excel file has the column formatted to Number and reimport but the problem was the same. I tried to manually change the type to "Whole Number Type" in Power BI but was given an error that that could not be done. Note the screenshot below.

Successful Import

In Excel, note the Formatting for "Test Step" column

 

In Power BI, note the summation for "Test Step" indicating that it recognizes the column to Whole Number Type

For all my other 3 files, this column "Test Step" could not be imported into Power BI as numerical to enable the summation functionality. 

I have tried formatting to Number and again as General in Excel. I have also recreate my file and set the format to Number before formatting my data to table. 

Can anyone help?

[v-yetao1-msft]

Hi @Anonymous 

I am so glad to hear you have solved your issue .You can accept your answer as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[amitchandak]

@Anonymous , power bi take these as a data source. so better to leave them in original format, sometime format may lead it to treat as text. Do not do any formatting in excel. That is my take on this.

[Anonymous · Accepted solution]

I found the solution to this. I had not noticed that there were text entries in the column which kept defaulting the column to text instead of making it numerical. 

[Anonymous]

Okay, I guess I should ask the question in a more direct way. How do I ensure that Power BI will import my data into Numerical? Thanks.