Power Query : how to get date from year and weekday?

[DM_EU]

Hello,

I have a table with Year and WeekDay and I need the whole date to link the table to my DimDate.

How I can I get the date from year and weekday?

Thank you

[NickA01]

Can you please advise what columns you have in your dimDate table. 
I'm thinking you could do something the lines of merging your Year_WeekNumber and Year_DayOfWeek in PowerQuery (Merge as New Join on Year, Expand then drop the second Year Column) 
Once you have this, dependent on what is in your DimDate, it sould be a simple multiColumn Look up against your DimDate. 

I'm using the RadCad DAX date dimension tbl from HERE
https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-...

Here's the PowerQuery script to join the tables 
let
Source = Table.NestedJoin(B_Year_WeekNumber, {"Year"}, A_YearDayOfWeek, {"Year"}, "YearDayOfWeek", JoinKind.LeftOuter),
#"Expanded YearDayOfWeek" = Table.ExpandTableColumn(Source, "YearDayOfWeek", {"Year", "WeekDay"}, {"YearDayOfWeek.Year", "YearDayOfWeek.WeekDay"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded YearDayOfWeek",{"YearDayOfWeek.Year"})
in
#"Removed Columns"

And the DAX for the lookup in tabl AM_MergeTbls

DATE = LOOKUPVALUE('Date'[Date],'Date'[Year],AB_MergedTbls[Year]//Find the Year

,'Date'[Week of Year],AB_MergedTbls[WeekNumber] //Find the Week Number

,'Date'[Day of Week],AB_MergedTbls[YearDayOfWeek.WeekDay])//Find the dayNUmber

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[NickA01]

Hi
Can you provide a screenshot ofyour date. 
With just Year and WeekDay this would not be possible
EG Year = 2022, WeekDay = 1 (Sunday)  
This could be any Sunday within the year. 
Now, WeekDay is structured to take in a date and Returns the interger for the day  
WEEKDAY(<date>, <return_type>) -- return_type is the index for the First day of the week (by default 1 = Sunday)
Weekday ('25 May 2022',1) would return 4 as when Sunday is first day of week then Wednesday is the 4th day.
weekday ('25 May 2022',2) would return 3 as when Monday is first day of week then Wednesday is the 3rd day.

https://docs.microsoft.com/en-us/dax/weekday-function-dax

If you are using WeekDay, then you must have a date. 

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[DM_EU]

Yes sorry you are right, it can be any weekday of the year...

But actually I have a second table with "year" and "week number". In this date table, is it possible to retrieve the date?

Thank you