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 "Year_WeekNumber" like "2020_19"
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?

 

[alansidman]

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"

Book6
	D	E
1	RFT	Week
2	5/19/2021	2021_20
3	2/3/2021	2021_5
4	5/20/2021	2021_20
5	2/3/2021	2021_5
6	7/18/2021	2021_29
7	3/24/2021	2021_12
8	3/24/2021	2021_12
9	3/24/2021	2021_12
10	3/24/2021	2021_12
Sheet1

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Power Query Date Week Of Year
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[e103050_1]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Power Query Date Week Of Year
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I am sorry about forgetting for providing the cross posting link
Thank you very much alansidman

 

[Joe4]

I am sorry about forgetting for providing the cross posting link

No worries!
Just be sure to do that if you Cross-Post in the future, and it won't be a problem.