Excel Number columns read as Text

[veronicaxge]

I have number columns with emty cells in Excel. When I connect to Power BI, these columns are autodetected as text columns, instead of Number column.

Is there a setting such that Power BI will detect these columns as Numbers? 

(I am aware that I could click on "Data type: Text" dropdown and change them back to Numbers but I have hundreds of these columns. Clicking one by one is what I hope to avoid...)

Thanks for answering this! 

[Anonymous]

Hi @veronicaxge ,

You can turn on the feature “Automatically detect column types and headers for unstructured sources” in Power BI desktop to auto detect data type by following steps. If this option is unchecked, the data type of each column be defaulted to Text. You can find more details in this documentation.

1. Open Power BI desktop
2. Click File -->Options and Settings -->Options
3. CURRENT FILE --> Data Load, check the option “Automatically detect column types and headers for unstructured sources” under Type Detection tab

         

     4.   Re-connect the data to the previous excel file

Best Regards

Rena

[Adamtall]

Hi,

press on a column then press Ctrl A and press "Detect Data Type"

If you have some columns that needs to be unmarked then hold Ctrl and press on the column.

/Adam

[rajulshah]

Hello @veronicaxge,

You can use SHIFT or CTRL key to change the data type. I don't know any of other method to make this easier.

[vivran22 · Accepted solution]

Hello @veronicaxge 

Ideally, if the source column formatting is number, Power BI/Query will pick accordingly. In case if it doesn't, then you can define it using Power Query Advanced Editor:

You might find a row in the Advanced Editor #" Changed Type" with the list of the column names along with the column properties {"Column Name" , type "Column properties"}

For setting the column properties as a decimal number you should use {"Clm Name", type number }

You have to do this setting once and going forward, Power BI will format the columns accordingly.

Br,

Vivek

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!