Forum Discussion
Best practives when transforming data in PowerQuery
"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.
"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.
Thanks HotChilli.
I might flag the issue and send back to the internal team performing the extractions from the database