Transform column data with calculations

Hello DarrenLau ,

The problem is, the date table from SQLBI.com is only in DAX, it is not ever in PowerQuery so other PowerQuery items can't reference it.

Starting with some M code from exceleratorbi.com https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

I put together a PowerQuery that you can use to get the table you need.  I also put up a quick video on how to add the table to PowerQuery https://www.youtube.com/watch?v=Kr-M8vWv-DE

From PowerBI desktop

• Get Date > Blank Query
• In PowerQuery window click on Advanced Editor
• Paste in the code below and change the StartDate and Enddate to be the range of dates you need to cover the quota period
• Click done
• Rename the query

let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    StartDate = #date(2018, 1, 1),
    EndDate = #date(2020, 12, 31),
    Length = Duration.Days(EndDate - StartDate),
    Custom1 = #"Changed Type",
    #"Inserted Start of Quarter" = Table.AddColumn(Custom1, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Added Custom2" = Table.AddColumn(#"Inserted Start of Quarter", "day of qtr", each Int16.From ( [Date] - [Start of Quarter]) + 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Custom2", "week of qtr", each if [day of qtr] > 90 then 13 else Number.IntegerDivide([day of qtr] + 6, 7), Int64.Type),
    #"Removed Duplicates" = Table.Distinct(#"Inserted Integer-Division", {"Start of Quarter", "week of qtr"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"day of qtr", "week of qtr"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Start of Quarter", "Date"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Start of Quarter", "Quarter Start Date"}, {"Date", "Week Start Date"}})
in
    #"Renamed Columns1"

This will give you the table of [Quarter Start Date] and [Week Start Date] that you need for the earlier solution.