•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Power query - date error
Our take
Are you struggling with date errors in Power Query? Specifically, when your data includes entries like 2025/99 that should interpret as 1st December 2025, you’re not alone. These inconsistencies can hinder your data transformation process. Fortunately, there’s a clear solution to ensure that Power Query reads these entries correctly alongside others like 2025/01 and 2025/12. In the following guide, we’ll walk you through a straightforward method to resolve these date discrepancies and streamline your data loading process. Let’s transform those errors into accurate insights!
I have some data in a column that reads 2025/01 which corresponds to 1st Jan 2025. 2025/03 corresponds to 1st March 2025, 2025/12 corresponds to 1st Dec 2025 etc etc.
I have a few lines with 2025/99 - these need to also be interpreted as 1st December 2025 (same as 2025/12). How do i get it to read these lines as such?
When loading the data for transformation, i am getting errors on the 2025/99 lines.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Issue with decimal numbers when importing a CSV file with Power QuerySo in my organization we have a database with CSV files that different departments need from time to time. Lately we have had some problems when importing this data to Excel with Power Query. It seems like the numeric columns, specially the ones with decimal numbers, get all f*** up when importing the files this way. For example (I am in Europe and we use commas for decimals and dots for the thousands): -Correct number: 155.746,88 -CSV imported: 15.574.688,00 As you can see, for some reason it seems that all the dots and commas are eliminated, then the ,00 decimal is added at the end of the number and then the number is "recalculated" based on these new decimal position. I can't change it "automatically" with power query because the numbers that don't have decimals are affected but don't change in value: -Correct number: 1.420 -CSV imported number: 1.420,00 Besides, in Power Query the data type of the columns affected appears in "Text", and when I change it to any numerical value (don't matter if it is decimal number, whole number, currency...) it gives me an error. Any ideas on why this happens and how to fix it? Thank you and have a good day! submitted by /u/Almogaver95 [link] [comments]
- Nested functions in Power Query (text into date)Hey there! I'm a bit lost with M syntax when nesting functions. I regularly parse monthly reports with Power Query where the current month is a text field composed of the last two letters of the year and the month, like for example 2411 is November of 2024. I do not like that. I always want date to be a proper date, so I would try and convert 2411 to 1/11/2024 (in d/m/y format). An easy solution for this with short reports is to create a column from example where I plug in the date I want in a few rows and PQ usually gets the idea pretty fast. But when it comes to longer tables this often does not work. It seem to work for rows that I see but when I load up the query following rows are often wrong or just empty. So it is safer to use a function. What works for me is to create multiple columns. First I create a [year2] column with =Text.Start([yearmonth],2) which I then set to number. I then create a [year4] column by simply using = [year2]+2000 (just ignore the last century for now. I haven't yet received date from before 2006 in more than a decade. I don't even think that data exists anywhere anymore.) Then I create a [month2] column with =Text.End([yearmonth],2) and set it also to number. And finally I tie it all together with =#date([year4],[month2],1) I will set it as date and eventually format it so that only the year and the month will show. But this seems overly complicated and tiresome, so I'd like to do it all in a single step. But when I want to do that with = #date( Text.Start([yearmonth],2) + 2000 , Text.End([yearmonth],2) , 1) I only get an error. Even when I turn to usual Excel solutions, such as multiplying the result of a text function with 1 to get a number as a result. It would work in plain Excel, it does not work in M. How can I tie text function results together into a nice, lovely date? How can I trick M to accept a text as a number? Thanks a bunch in advance! submitted by /u/Laxativus [link] [comments]