Hi all,
I need to calculate the number of weeks included in a month for recurring dates. I have managed to achieve this result with a combination of power query and power pivot but I have realized now that I need to achieve this only with power query (I need to group by AFTER performing some calculations with the number of weeks).
The formulas used in power pivot are:
=COUNTROWS(FILTER(Table1,Table1[Start of Month]=EARLIER(Table1[Start of Month])))
and
=CALCULATE(COUNT(Table1[Date]),ALLEXCEPT(Table1,Table1[Date]))
The above formulas are both included in the "Helper1" and "Helper2" column in Power Pivot and I used them to calculate the number of weeks in the referring month.
Is there anyone able to achieve this in power query without using group by? I will be grateful forever 😀
You could do something like this:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Weeks", "Weeks - Copy"),
WeeksToDates = Table.ReplaceValue(#"Duplicated Column",each [Weeks],each Date.AddDays(Date.EndOfWeek(DateTime.LocalNow(),Day.Friday),(Number.From(Text.Replace([Weeks], "plans_week", "")) -1) * 7),Replacer.ReplaceValue,{"Weeks"}),
#"Renamed Columns" = Table.RenameColumns(WeeksToDates,{{"Weeks", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
DateList = #"Changed Type"[Date],
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Weeks - Copy", "Weeks"}}),
#"Inserted Start of Month" = Table.AddColumn(#"Renamed Columns1", "Start of Month", each Date.StartOfMonth([Date]), type date),
SOMs = #"Inserted Start of Month"[Start of Month],
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Start of Month",{"Weeks", "Date", "Start of Month"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each let som = [Start of Month], d = [Date] in List.Count(List.Select(SOMs, each _ = som)) / List.Count(List.Select(DateList, each _ = d)), Int64.Type)
in
#"Added Custom"
Hi Velouria,
Thank you very much for your help. Unfortunately I won't be able to use it because my original data set has hundreds of thousands of rows and that formula kills the workbook.
However I really liked the way you replaced the week-ending dates. Would you please be able to advise how would you change that formula with unpivoted columns (i.e. plans_week* in each column)? See it attached please.
Sure - you can do something like this:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RenamedColumns = Table.TransformColumnNames(Source,
(cn as text) as text =>
if Text.Start(cn, 10) = "plans_week" then Date.ToText(Date.AddDays(Date.EndOfWeek(DateTime.Date(DateTime.LocalNow()),Day.Friday), (Number.From(Text.Replace(cn, "plans_week", "")) -1) * 7), "dd/MM/yyyy") else cn)
in
RenamedColumns
Brilliant! Thanks!
