I’ve added ISO Quarters in the attached pbix. In the script itself (should you want to add it), here are the lines added:
// ---------------------------------------------------
// ISO QUARTER: for Melissa to review before adding
InsertISOQuarter = Table.AddColumn(InsertWeeknYear, "ISO Quarter", each if [ISO Weeknumber] <= 13 then "Q1" else if [ISO Weeknumber] <= 26 then "Q2" else if [ISO Weeknumber] <= 39 then "Q3" else "Q4", type text),
InsertISOQtrYear = Table.AddColumn(InsertISOQuarter, "ISO Quarter & Year", each [ISO Quarter] & " " & Number.ToText([ISO Year]), type text),
InsertISOQtrnYear = Table.AddColumn(InsertISOQtrYear, "ISOQuarternYear", each [ISO Year] * 10000 + Number.FromText(Text.End([ISO Quarter],1)) * 100, Int64.Type),
// ---------------------------------------------------
. ABOUT ISO Quarters
(just some additional info for everyone to read)
ISO-8601 calendars have a consistent number of weeks in each quarter and a consistent number of days > each week, making the ISO-8601 calendar popular when calculating retail and financial dates.
The first three quarters in the ISO-8601 always have 13 weeks in them, with the last Quarter having either 13 or 14 weeks in it, depending upon the start of the next ISO-8601 year.
If making some revisions, please include Fiscal Day of Year , Fiscal Week of Year & Fiscal PYTD. I think those would be great additions. I currently do most of these as calculated columns, but know that Power Query is better suited for this.
Hello.
Thank you, the table is really powerful.
I copied and pasted the function in Power Query and tried to convert the column “Month & Year”, which is generated automatically, into Date format. It worked fine, except for the month of “March” . I can see that it gives me an error for March only for all the years.
I have a file with Monthly budget data (month and year) and I’m trying to link the DateTable with these dates via TREATAS function. Works fine, but it excludes the month of March.
Can anyone help me on this?
Thank you very much.
Ok thanks @Melissa, will read up about it then copy your code. Seems a bit complex for my bi noob brain. So I’m using the date table from this original post, do i then create 2 more tables using your code?
I used in the past prior versions of this Date Table which has been significantly improved thru time.
Why is it that in the first post within this thread you were covering how to convert it from a function to a true table (hence allowing modification using the PQ bar) whereas now I have the feeling you’d recommend users to use the function table instead - is my understanding correct?
I’m attempting to convert this function Date table into a table but struggling a bit due to the immense code that has been added (specially not sure how to treat the documentation part of it).
For “ease of use” just use the Date table M function as a function. However should you need to make modifications, turning this function query into a table query is extremely helpful.
I understand that with all the updates, it has become a bit more difficult to translate the instructions given for that proces, so I have updated the image in the initial post above.
Love this date table and the thread, great example of a great community!
I started using it for all my new reports a few months back and trying to go through my old reports to update those also. I love going through the process of making the start and end dates dynamic.
Yesterday night I went thru the whole thread and I would swear there was 2 videos of yours, being the first one used to showcase how could we convert from fx to tables (the second one is the one currently being shown in this thread - to remove/adjust columns in the Advance Editor). If I remember this video well, you had to rename the first variable (StartDate) and had to use it further down in the code to make it work - am I correct?
If you could please be so kind to publish again the first video converting from fx to table that would be great.
Last but not least, I think there’s no WeekNum (1, 52…) in the code (there’s the ISO Weeknumber though) - unless I’m missing something. If we could have this too that would really improve.
You are missing a closing parenthesis in the EndDate variable declaration.
I removed the link to the video on how to turn your function query into a table query because it no longer matches the image in the initial post above, which could be confusing. You can find that here.
I think you might be referring to this video…
This is an ISO-8601 calendar. But you can make your own modifications and/or additions of course.
These ISO-8601 week attributes are currently included.
Superb @Melissa! Thanks so much! Yes I agree, I have calculated WeekNum myself quite easily using Power Query ribbon functions which for newbies like I is far way more intuitive
If you find the data table has too many columns, use Chose Columns at the end to just leave what you need. That’s what I’ve done with a few of my reports. Stripped them down to only the columns I require.
