power query to take date input

[bbrimberry]

Hello,

I am curious if someone can help me solve for this.
I would like to be able to take input from the user or store in the input in a cell

I have the table below.
I would like to take the input from the user such as 02/01/2020 and let power query fill the date table
I have a macro that does this but I would like to solve for it in power query.
Is this possible?

date.xlsx
	A	B
1	Week and Day	Real Date
2	Week 1 Monday	02/01/2021
3	Week 2 Monday	02/08/2021
4	Week 3 Monday	02/15/2021
5	Week 4 Monday	02/22/2021
6	Week 1 Tuesday	02/02/2021
7	Week 2 Tuesday	02/09/2021
8	Week 3 Tuesday	02/16/2021
9	Week 4 Tuesday	02/23/2021
10	Week 1 Wednesday	02/03/2021
11	Week 2 Wednesday	02/10/2021
12	Week 3 Wednesday	02/17/2021
13	Week 4 Wednesday	02/24/2021
14	Week 1 Friday	02/05/2021
15	Week 2 Friday	02/12/2021
16	Week 3 Friday	02/19/2021
17	Week 4 Friday	02/26/2021
18	Week 1 Thursday	02/04/2021
19	Week 2 Thursday	02/11/2021
20	Week 3 Thursday	02/18/2021
21	Week 4 Thursday	02/25/2021
Sheet1

 

[sandy666]

you can try

// 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},
    C2T = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Type = Table.TransformColumnTypes(C2T,{{"Column1", type date}}),
    Rename = Table.RenameColumns(Type,{{"Column1", "Date"}}),
    WeekM = Table.AddColumn(Rename, "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    Day = Table.AddColumn(WeekM, "Day Name", each Date.DayOfWeekName([Date]), type text),
    Group = Table.Group(Day, {"Day Name"}, {{"Count", each _, type table}}),
    Index = Table.AddIndexColumn(Group, "Index", 1, 1),
    Expand = Table.ExpandTableColumn(Index, "Count", {"Date", "Week of Month"}, {"Date", "Week of Month"}),
    Prefix = Table.TransformColumns(Expand, {{"Week of Month", each "Week " & Text.From(_, "en-GB"), type text}}),
    TCC = Table.CombineColumns(Prefix,{"Week of Month", "Day Name"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Week and Day"),
    Date = Table.TransformColumnTypes(TCC,{{"Date", type date}}),
    RC = Table.RemoveColumns(Date,{"Index"})
in
    RC

Parameter	Value		Week and Day	Date
StartDate	01/02/2021		Week 1 Monday	01/02/2021
EndDate	28/02/2021		Week 2 Monday	08/02/2021
			Week 3 Monday	15/02/2021
			Week 4 Monday	22/02/2021
			Week 1 Tuesday	02/02/2021
			Week 2 Tuesday	09/02/2021
			Week 3 Tuesday	16/02/2021
			Week 4 Tuesday	23/02/2021
			Week 1 Wednesday	03/02/2021
			Week 2 Wednesday	10/02/2021
			Week 3 Wednesday	17/02/2021
			Week 4 Wednesday	24/02/2021
			Week 1 Thursday	04/02/2021
			Week 2 Thursday	11/02/2021
			Week 3 Thursday	18/02/2021
			Week 4 Thursday	25/02/2021
			Week 1 Friday	05/02/2021
			Week 2 Friday	12/02/2021
			Week 3 Friday	19/02/2021
			Week 4 Friday	26/02/2021
			Week 1 Saturday	06/02/2021
			Week 2 Saturday	13/02/2021
			Week 3 Saturday	20/02/2021
			Week 4 Saturday	27/02/2021
			Week 1 Sunday	07/02/2021
			Week 2 Sunday	14/02/2021
			Week 3 Sunday	21/02/2021
			Week 4 Sunday	28/02/2021

 

[bbrimberry]

Wow...
I that's incredible. that solved my issue.
one last question.

I am in the states.
how would i get a 2 digit month, 2 digit day and 4 digit year?

Example

01/21/2021

 

[sandy666]

set date format
mm/dd/yyyy

or change system date to this format

 

[bbrimberry]

wonderful!!

thank you very much

 

[sandy666]

You are welcome & thanks for the feedback
Have a nice day