Best practives when transforming data in PowerQuery

[VanNostrand]

Hello everyone,

I would appreciate your feedback on a quick question:

What’s the best practice(s) in PowerQuery when we are uploading a new database, and face data like the example below:

• In the database (excel file) there are some values that should be number, but are in fact formatted as custom.
• For example: Excel displays as “9257.5”, but the formula bar “01/05/9740”.

• By default, PowerQuery defines the data type as whole number and therefore the value is “2.687.212”. The concern is that this value is much bigger than the remaining values.

Any suggestions on how to address cases like this ?

Many thanks

[HotChilli · Accepted solution]

"For example: Excel displays as “9257.5”, but the formula bar “01/05/9740”." - this doesn't match with the picture, so let's go with the picture.

Looks like it's formatted as a type of date - May in the year 9257.

All dates in excel are stored as a number and that date represents 2687212 (No of days since 1900 more or less I think)

---

So what to do. Maybe change the format in Excel.  I am unsure whether you are importing this from Excel into Powerbi or the source is external and coming into Excel.

--

If it can't be changed in Excel maybe convert to a date in Power Query then construct a number by parsing values from the date i.e. YEAR(theColumn) x 10 then add MONTH(theColumn) and convert to a number - that was completely off the top of my head, I don't know if it makes sense.

[VanNostrand]

Thanks HotChilli.
I might flag the issue and send back to the internal team performing the extractions from the database