Forum Discussion

veronicaxge's avatar
veronicaxge
Frequent Visitor
6 years ago
Solved

Excel Number columns read as Text

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 de...
Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    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!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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