Power Query Date Week Of Year

[e103050_1]

I have created a column in a power query that returns the week number of dates with week start day of Friday using the following;

= Table.AddColumn(#"Inserted Week of Year RFT", "W_RFT", each Date.WeekOfYear([RFT],Day.Friday), Int64.Type)

and another column for year;

= Table.AddColumn(#"Inserted Week of Year RFT", "Year", each Date.Year([START FAB]), Int64.Type)

but I need to a week column with the format of "Year_WeekNumber" like "2020_19"
I need a function like an excel formula "=YEAR(BC2)&"_"&WEEKNUM(BC2,15)"
target table as below;
anyone hep me to create function in excel power query?

RFT	Week
5/19/2021	2021_20
2/3/2021	2021_5
5/20/2021	2021_20
2/3/2021	2021_5
7/18/2021	2021_29
3/24/2021	2021_12
3/24/2021	2021_12
3/24/2021	2021_12
3/24/2021	2021_12

any idea?

 

[GraH - Guido]

Both columns in PQ need to be text. Then you combine them.
Sidenote: consider PQ does not return weeknumber according to ISO standards. In Europe that might be an issue.

 

[AlanSidman]

crossposted: https://www.mrexcel.com/board/threads/power-query-date-week-of-year.1189764/

Read this: https://www.excelguru.ca/content.php?184

 

[vletm]

e103050_1
... as well as You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

There are clear sentences, if someone uses Cross-Posting.

 

[e103050_1]

Answered in this link

Sorry for fergetting providing cross post link

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RFT", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([RFT]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Year", "Custom", each Date.WeekOfYear([RFT],Day.Friday)),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Year", type text}, {"Custom", type text}}, "en-US"),{"Year", "Custom"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Week")
in
    #"Merged Columns"