cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mb0307
Post Patron
Post Patron

Week/Year to date - Power Query

Hi,

 

I have Week/Year in a column and would like to convert into Friday of the week.

 

Example: 

wk31/2021 should return 06/Aug/20201

wk31/2021 shuld return 13/Aug/2021

 

I need this is Power Query only.

 

Thanks for your help in advance. 

3 REPLIES 3
mb0307
Post Patron
Post Patron

@Greg_Deckler Thanks and solution works perfectly for 2021 but 2022 dates are not for friday, see below:

 

Week Year.jpg

 

Any suggestion please? 

@mb0307 Well, they were for Friday, just the wrong Friday. Slight error, see if this one is better and if you can see the difference:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUNzIwMlSK1QGyjRBssLARmGkBZcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.AddWeeks(#date([Column1.2],1,1),[Column1.1]-1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Date.EndOfWeek([Custom],Day.Saturday)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1.1", "Column1.2", "Custom"})
in
    #"Removed Columns"

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Greg_Deckler
Super User
Super User

@mb0307 Try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUNzIwMlSK1QGyjaDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.AddWeeks(#date([Column1.2],1,1),[Column1.1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Date.EndOfWeek([Custom],Day.Saturday)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1.1", "Column1.2", "Custom"})
in
    #"Removed Columns"

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!