Forum Discussion

slohombre's avatar
slohombre
Brass Contributor
Aug 17, 2020

Power Query Date Extrapolation

Hi, all. Looking for some advice on how to extrapolate a date from two columns of information. I have a column that has week of year number (1,2,3,4, etc.) AND I have another column that has day name (Monday, Tuesday, Wednesday, etc). Is there a formula or function that would utilize info from these two columns and give me the precise date in the form of 1/1/2020 (for example)?

 

Please see pic attached.

 

Thanks, Paul

excel

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 20, 2020

    slohombre 

    For such sample

    you may do it with query

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom1" = Table.AddColumn(Source,
        "Date",
        each
            Date.AddDays(
                Date.StartOfWeek(
                    Date.AddWeeks(
                        #date(2020, 1, 1),
                        [Week Number]-1
                    ),
                    Day.Monday
                ),
                List.PositionOf(
                    {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},
                    [Day]
                )
            )
    )
in
    #"Added Custom1"

    but it very depends on which standard do you use for first week of the year and from which day week starts (here is from Monday).

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Aug 18, 2020

    slohombre I guess you can create a calendar table (manually or via Power Pivot copied back into an Excel sheet) that has at least date, week number  =WEEKNUM(date) and the day name =TEXT(date,"dddd") in it. Query it and then merge the query from your picture with this calendar query.

    Haven't yet figured out how (if possible) to merge a query with a calendar that sits in the Data Model. 

     

Resources