PowerQuery number columns becomes text in PowerPivot - how to avoid?

[xls_mycroft]

Hi,

(using Excel 2013, 32bit on a German system)

I'm having a slightly complex PowerQuery that is loaded to the DataModel/PowerPivot. The query does the following:
1. Load data from a MySQL DB, calculate some additional columns with Table.AddColumn
2. Load multiple other tables from MySQL, CSV and Excel
3. Perform left outer join between main table and other tables from (2) and (1)

When inspecting the result in PowerQuery, all looks as expected. However, in PowerPivot, the calculated columns from step (1) are imported as text (even though I used =

Table.AddColumn(..., type Number)

to define the column type. All other columns (also from the Table.Join), are imported correctly.

Changing the column type from Text to Decimal does not work due to regional settings. E.g. 50% is exported as text 0.5000. But as the client is using a German system with a , as the decimal separator, this becomes 5000.

Any idea how to keep the column type between PowerQuery and PowerPivot?

Thanks!

 

[xls_mycroft]

Ok, found the answer myself:

When I first imported the data to PowerPivot, I did not use the type Number parameter and PowerPivot imported the column as text. The subsequent changes of the type in PowerQuery were not carried forward to PowerPivot. So I unloaded the query from the data model - and reloaded it again, this time with the types set from the start - and all works as expected!