Count selected day between dates
Parameter Value WeekDay CountStartDate 25/05/2020Monday 4EndDate 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