Aug 17 2020 08:14 AM - edited Aug 17 2020 08:41 AM
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
Aug 18 2020 03:18 AM
@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.
Aug 20 2020 03:43 AM
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).