power query to take date input

bbrimberry

New Member
Joined
Mar 23, 2016
Messages
34
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
AB
1Week and DayReal Date
2Week 1 Monday02/01/2021
3Week 2 Monday02/08/2021
4Week 3 Monday02/15/2021
5Week 4 Monday02/22/2021
6Week 1 Tuesday02/02/2021
7Week 2 Tuesday02/09/2021
8Week 3 Tuesday02/16/2021
9Week 4 Tuesday02/23/2021
10Week 1 Wednesday02/03/2021
11Week 2 Wednesday02/10/2021
12Week 3 Wednesday02/17/2021
13Week 4 Wednesday02/24/2021
14Week 1 Friday02/05/2021
15Week 2 Friday02/12/2021
16Week 3 Friday02/19/2021
17Week 4 Friday02/26/2021
18Week 1 Thursday02/04/2021
19Week 2 Thursday02/11/2021
20Week 3 Thursday02/18/2021
21Week 4 Thursday02/25/2021
Sheet1
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
you can try
Power Query:
// 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
Power Query:
// 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
ParameterValueWeek and DayDate
StartDate01/02/2021Week 1 Monday01/02/2021
EndDate28/02/2021Week 2 Monday08/02/2021
Week 3 Monday15/02/2021
Week 4 Monday22/02/2021
Week 1 Tuesday02/02/2021
Week 2 Tuesday09/02/2021
Week 3 Tuesday16/02/2021
Week 4 Tuesday23/02/2021
Week 1 Wednesday03/02/2021
Week 2 Wednesday10/02/2021
Week 3 Wednesday17/02/2021
Week 4 Wednesday24/02/2021
Week 1 Thursday04/02/2021
Week 2 Thursday11/02/2021
Week 3 Thursday18/02/2021
Week 4 Thursday25/02/2021
Week 1 Friday05/02/2021
Week 2 Friday12/02/2021
Week 3 Friday19/02/2021
Week 4 Friday26/02/2021
Week 1 Saturday06/02/2021
Week 2 Saturday13/02/2021
Week 3 Saturday20/02/2021
Week 4 Saturday27/02/2021
Week 1 Sunday07/02/2021
Week 2 Sunday14/02/2021
Week 3 Sunday21/02/2021
Week 4 Sunday28/02/2021
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,223,744
Messages
6,174,252
Members
452,553
Latest member
red83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top