Count selected day between dates
Parameter |
Value |
|
WeekDay |
|
Count |
StartDate |
25/05/2020 |
|
Monday |
|
4 |
EndDate |
15/06/2020 |
|
|
|
|
Code:
// 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
Bookmarks