Power Query Challenge - Repeat Rows for Courses across Years

Power Query
1

Please use this file for the Power Query challenge -
Course Challenge.pbix (56.7 KB)
Course Challenge.xlsx (10.2 KB)

Task -

You need to generate new rows based on how many years a particular course spans, for example Python, the course ran from 2018-03-01 till 2020-12-30 so there are a total of 3 years.

Once you have this number generate the rows for the next years considering the Start and End Date should be for the next year.

So for the first & last year the start and end date respectively will be from original data. whereas for the years in between you need to return start and end date of Gregorian calendar.

And if the course ran only for 1 year then just return the original row.

image
image766×665 33 KB

1 Like
2

Course Challenge - JB.pbix (35.3 KB)

Probably not the most efficient way but happy managed it!

2 Likes
3

Hi @AntrikshSharma,
Very good initiative to practice and improve our Power Query skills.

let
 Source = Table.FromRows(
   Json.Document(
     Binary.Decompress(
       Binary.FromText(
         "i45WcnGMUNJRMtc3tNQ3MjC0BLKBDHMg28hAKVYnWimgsiQjPw8obKxvCFJhAWQaGukbGyApyS9PLVIILE0tqgRJgtUZGULVQTkgdcEZiUWpBfmZeSUgOaA6I5iVFvoWIGUmEGWBPkAhU30zkJARxBgjUwgnNhYA",
         BinaryEncoding.Base64
       ),
       Compression.Deflate
     )
   ),
   let
     _t = ((type nullable text) meta [Serialized.Text = true])
   in
     type table [Course = _t, #"Start Date" = _t, #"End Date" = _t]
 ),
 ChangedType = Table.TransformColumnTypes(
   Source,
   {{"Course", type text}, {"Start Date", type date}, {"End Date", type date}}
 ),
    #"Added Years" = Table.AddColumn(ChangedType, "Years", each {Date.Year([Start Date]) .. Date.Year([End Date])}),
    #"Expanded Years" = Table.ExpandListColumn(#"Added Years", "Years"),
    #"Added Start Dates" = Table.AddColumn(#"Expanded Years", "Start Dates", each if Date.Year([Start Date]) = [Years] then [Start Date] else if Date.Year([Start Date]) < [Years] then Date.StartOfYear(#date([Years], 1, 1 ) ) else null, type date),
    #"Added End Dates" = Table.AddColumn(#"Added Start Dates", "End Dates", each if Date.Year([End Date]) = [Years] then [End Date] else if Date.Year([End Date]) > [Years] then Date.EndOfYear(#date([Years], 1, 1 ) ) else null, type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added End Dates",{"Course", "Start Dates", "End Dates"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Start Dates", "Start Date"}, {"End Dates", "End Date"}})
in
    #"Renamed Columns"

image
image1366×729 52.4 KB

Regards,

Course Challenge_JAFP.pbix (74.0 KB)

2 Likes
4

This post is part of the Enterprise DNA platform initiative to provide information about Power Query Challenge.

To ask any questions on the content covered in this category please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum

Thank you