Week No with Month and year no

[vat2do]

Hope you have great day,

I need support to get the out put in new column as "Week 1 January 22" from the date table created using calender auto showing one year dates in power query.

Thanks in advance

[ryan_mayu · Accepted solution]

@vat2do 

just like what @Rudy_D  said, you can't create the column in PQ if you use dax to create a table.

pls try this

Column = "Week " & WEEKNUM('Table'[Date],2) &" "& format('Table'[Date],"mmmm")&" "& right(year('Table'[Date]),2)

or 

Column 2 = "Week " & ROUNDDOWN(('Table'[Date]-min('Table'[Date]))/7,0)+1 &" "& format('Table'[Date],"mmmm")&" "& right(year('Table'[Date]),2)

[vat2do]

i created simple date table with column name "Date" using "CALENDARAUTO()" expression

[Rudy_D]

If you used Calendarauto(), I guess you have created the table in Dax.

So you cannot add the column requested using power query. But I can show you how to do it in Dax.

[vat2do]

Yes i am using DAX, appreciated to have a solution thanks

[Rudy_D]

You can use this formula :

Newcolumn = CONCATENATE("Week ",CONCATENATE(WEEKNUM('Table'[Date]), CONCATENATE(" ", CONCATENATE(FORMAT('Table'[Date], "MMM"), CONCATENATE(" ", YEAR('Table'[Date]))))))

[vat2do]

hi,

your solution is useful to me, another quick question, how i can repeat the week numbers when a month name changes like week 1, week2, week3 and week 4 for January but i need to repeat starting from week 1 to week 4 in february and so on

[ryan_mayu]

@vat2do 

you can try this

Column 2 = "Week " & ROUNDDOWN(('Table'[Date]-min('Table'[Date]))/7,0)+1 &" "& format('Table'[Date],"mmmm")&" "& right(year('Table'[Date]),2)

[Rudy_D]

Hi,

Could you please describe the date table from which you want to create the new column ?

Thanks