Forum Discussion
Dynamic variable in PQ to compare and calculate total
Hello Anonymous , here is an example of what you could do with PQ:
let
Source = Excel.CurrentWorkbook(){[Name="DatesDiff"]}[Content],
typed = Table.TransformColumnTypes(Source,{{"Post date", type date}, {"Unpost date", type date}}),
datesListsAdded = Table.AddColumn(typed, "Dates list", each List.Dates([Post date], Duration.Days([Unpost date] - [Post date]) + 1, #duration(1, 0, 0, 0))),
datesListsExpanded = Table.ExpandListColumn(datesListsAdded, "Dates list"),
count = List.Count(List.Distinct(datesListsExpanded[Dates list]))
in
countThe global behavior is to generate a list of all dates between each post and unpost date, expand these lists, and count the number of distinct dates.
With your sample data, I get 162 days, which is what I also find with manual calculation (you are missing some days in your own calculation, you should recheck it).
Hope this helps.
Hello Anonymous , here is an example of what you could do with PQ:
let
Source = Excel.CurrentWorkbook(){[Name="DatesDiff"]}[Content],
typed = Table.TransformColumnTypes(Source,{{"Post date", type date}, {"Unpost date", type date}}),
datesListsAdded = Table.AddColumn(typed, "Dates list", each List.Dates([Post date], Duration.Days([Unpost date] - [Post date]) + 1, #duration(1, 0, 0, 0))),
datesListsExpanded = Table.ExpandListColumn(datesListsAdded, "Dates list"),
count = List.Count(List.Distinct(datesListsExpanded[Dates list]))
in
count
The global behavior is to generate a list of all dates between each post and unpost date, expand these lists, and count the number of distinct dates.
With your sample data, I get 162 days, which is what I also find with manual calculation (you are missing some days in your own calculation, you should recheck it).
Hope this helps.
Thank you very much, it works like a charm!
And yes I calculated my own sample data wrong... 😣