If you search the internet for a calculation of the ISO calendar week in Power Query, you will often find a Custom Function that does the job. But unfortunately the Custom Function interrupts the query folding for SQL data sources, which mostly slows down the loading process. In this post I provide a solution that also supports query folding for SQL data sources.
The calculation is based on the blog post „A Simple Formula to Calculate the ISO Week Number“ by Jeff Moden, which I translated to SQL Server query folding capable Power Query transformations.
In order not to unnecessarily inflate the application steps, I have combined all transformations into one application step.
In order to transfer the application step into the own query, the lines 5 – 23 have to be copied and pasted into the own query via the advanced editor. In line 11, the application step name „dbo_FactSales“ and the date column name „Datekey“ must be adapted to the corresponding application step name and date column name of the query. Thereby the date column must be of the data type Date or Date/Time.
let
Source = Sql.Databases(".", [CreateNavigationProperties = false]),
ContosoRetailDW_SQLBI = Source{[Name = "ContosoRetailDW_SQLBI"]}[Data],
dbo_FactSales = ContosoRetailDW_SQLBI{[Schema = "dbo", Item = "FactSales"]}[Data],
IsoWeek = Table.TransformColumns(
Table.TransformColumns(
Table.TransformColumnTypes(
Table.TransformColumns(
Table.TransformColumns(
Table.TransformColumnTypes(
Table.DuplicateColumn(dbo_FactSales, "DateKey", "IsoWeek"),
{{"IsoWeek", Int64.Type}}
),
{{"IsoWeek", each _ + 53688, type number}}
),
{{"IsoWeek", each _ - Number.Mod(_, 7) - 53685, type number}}
),
{{"IsoWeek", type date}}
),
{{"IsoWeek", Date.DayOfYear, Int64.Type}}
),
{{"IsoWeek", each Number.RoundDown(_ + 6 / 7), Int64.Type}}
)
in
IsoWeek
Performance comparison
In an initial performance comparison of calculating the ISO calendar week using a power query function (red) versus the query folding solution (green), the query folding solution was approximately 1 minute faster for approximately 3.4 million rows (FactSales of ContosoRetailDW).
Sources:
https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
https://whatthefact.bi/de/power-bi/power-query-m/iso-8601-kalenderwoche-in-power-query-berechnen/
