Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a database where the dates are integers in YYYYMMDD format - 20191231 is Dec 31, 2019.
I obviously cannot just subtract, because 20200101 (Jan 1, 2010) - 20191231 should be 1 day, but it is 8,870.
I'm trying to find a way to do math to add/subtract days without converting to dates. The reason is any conversion breaks query folding. The cleanest way I know to do it is:
Date.FromText(Text.From(20191231))
That breaks folding in half a second.
But these dates have been used for decades by systems and I am just wondering if anyone knows a formula or way date math was done with them. Or have the apps that use dates in this format always parsed the data out into YYYY MM DD then converted to a date internally?
EDIT: Just want to clarifiy. I did not mark the below answer as a solution. It does not answer the question. If you are trying to generate a query on Power Query and you use the answer below, it will not work without breaking folding, which was the point of my question.
In fairness, I am not sure there is a solution. It seems PQ doesn't support what I was trying to do, and sometimes the answer is, there is no answer. It isn't to have some forum admin come in and mark something as a solution that is misleading.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSolved! Go to Solution.
Hi @edhans ,
If you want to convert the YYYYMMDD on the SQL query before uploading on PBI you can use the SQL function
CONVERT(CHAR(10), COLUMN, 120)
If you want to do it directly on PBI the best way is to create the calculated column an then delete the text column, for Power Query as you know what is considered in the model is the latest table on your query so the additional columns that you delete won't show up or make "weight" on your model.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @edhans ,
Not really sure if I understand your question but try to add a calculated column using you code but making some changes:
Duration.Days( Date.FromText(Text.From([Column2])) - Date.FromText(Text.From([Column1])))
can you share some more data around your issue what do you exactly want to achieve.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYeah, I could use a calculated column. I guess my question is more theoretical. For example, systems that use the Julian Date system have a whole set of formulas around which it operates, and being plain math, that will fold in a SQL statement, so 1 row or 1 million, the server does all the work.
I was trying to find out if anyone had experience with the YYYYMMDD format, which is kind of common, and how people did date math with it. But everything I look up is by converting it to a real date first. Even in SQL, but Power Query won't generate the SQL statements in folding to do that work.
There may not be an answer short of true date conversion. But I figured there were a lot of people here working on various systems and might know how DBAs or programmers dealt with it.
Of course, if anyone can convert YYYYMMDD to a true date in Power Query that will fold, that would solve all of my problems.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans ,
If you want to convert the YYYYMMDD on the SQL query before uploading on PBI you can use the SQL function
CONVERT(CHAR(10), COLUMN, 120)
If you want to do it directly on PBI the best way is to create the calculated column an then delete the text column, for Power Query as you know what is considered in the model is the latest table on your query so the additional columns that you delete won't show up or make "weight" on your model.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |