Hi Jamal,
I found a way to replace the code directly. You can check it out in this file.
1. Create a function. (I found the M code here).
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertMonth = Table.AddColumn(RenamedColumns, "Month", each Date.Month([Date])),
InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
InsertYear = Table.AddColumn(InsertMonthName, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "Month Year", each Date.QuarterOfYear([Date]))
in
InsertQuarter
in
CreateDateTable2. Replace the M code of the query dDate with the following one.
let
maxDate = List.Max(#"Consolidated Data"[Column1]), //Max date of another query, which makes this query dynamic.
Source = Query(#date(2018, 2, 1), maxDate, null)
in
Source3. Apply the changes.
Note: If the data you shared is a sample, you need to adjust the code to satisfy your data. The structure and the column names should be the same.
Now you can get rid of the workbook file.
Best Regards,
Dale
Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
