PQ - Count selected day between dates

[sandy666]

Count selected day between dates



Parameter
Value
WeekDay

Count

StartDate
25/05/2020Monday
4

EndDate
15/06/2020



// WeekDay
let
Source = Excel.CurrentWorkbook(){[Name="WeekDay"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"WeekDay", type text}})
in
Type

// fnGetParameter
(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

// Calendar
let
startdate = Number.From(fnGetParameter("StartDate")),
enddate = Number.From(fnGetParameter("EndDate")),

Source = {startdate..enddate},
ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Type = Table.TransformColumnTypes(ToTable,{{"Column1", type date}}),
DayName = Table.AddColumn(Type, "Day Name", each Date.DayOfWeekName([Column1]), type text)
in
DayName

// Merge1
let
Source = Table.NestedJoin(WeekDay,{"WeekDay"},Calendar,{"Day Name"},"Calendar",JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Source, "Calendar", {"Day Name"}, {"Day Name"}),
Group = Table.Group(Expand, {"Day Name"}, {{"Count", each Table.RowCount(_), type number}}),
TSC = Table.SelectColumns(Group,{"Count"})
in
TSC