cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BlueNote
Frequent Visitor

Add new rows based on most recent Date change

Hi Everyone,

 

This is something that is far beyond my current PQ abilities.

 

I need to get a table that looks like this (original table):

 

Employee NameDate Request FilledReason Code
John DoeJan-201- New Hire
John DoeAug-202- Job Change

 

To look like this (Tran:

 

Employee Name    Date     Most Recent Reason Code
John Doe Jan-20    1- New Hire
John Doe Feb-20   1- New Hire
John Doe Mar-20   1- New Hire
John Doe Apr-20   1- New Hire
John Doe May-20   1- New Hire
John Doe Jun-20   1- New Hire
John Doe Jul-20    1- New Hire
John Doe Aug-20    2- Job Change
John Doe Sep-20   2- Job Change
John Doe Oct-20   2- Job Change
John Doe  Nov-20   2- Job Change
John Doe Dec-20   2- Job Change
John Doe Jan-21   2- Job Change
John Doe Feb-21   2- Job Change

 

So basically the logic is: for each row in the original table find the next row with the next latest date and the same employee name and insert new rows for the months between the dates using the same employee name and reason code. This needs to iterate on all rows until a user specified final date (Feb 2021 in this examle) is reached.

 

Thanks for any help.

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @BlueNote 

 

try this solution. It groups first the data by name... because I suppose you will have more then 1 name in your table. Afterwards I add a Index column to the grouped table and add new column that creates a list of dates of every month till the next event. If no event is found, the last month is used. Here the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lHySszTNTIAMgx1FfxSyxU8MotSlWJ1UNQ4lqZD1BjpKnjlJyk4ZyTmpQNVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Date Request Filled" = _t, #"Reason Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Request Filled", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name"}, {{"AllRows", each 
    let 
        AddIndexAndSort = Table.AddIndexColumn(Table.Sort(_, {{"Date Request Filled", Order.Ascending}}), "Index", 1,1),
        AddListWithDates = Table.AddColumn
        (
            AddIndexAndSort,
            "Date Request Filled New",
            (add)=> let 
                GetEndDate = try Table.SelectRows(AddIndexAndSort, each [Index]= add[Index]+1)[Date Request Filled]{0} otherwise Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())),
                GetListOfDates = List.Transform(List.Numbers(0,((Date.Year(GetEndDate)-Date.Year(add[Date Request Filled]))*12)+ (Date.Month(GetEndDate)-Date.Month(add[Date Request Filled])) ,1), each Date.AddMonths(add[Date Request Filled], _))
            in 
             GetListOfDates
        )
        
        
    in 
     AddListWithDates }}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date Request Filled", "Reason Code", "Date Request Filled New"}, {"Date Request Filled", "Reason Code", "Date Request Filled New"}),
    #"Expanded Date Request Filled New" = Table.ExpandListColumn(#"Expanded AllRows", "Date Request Filled New")
in
    #"Expanded Date Request Filled New"

this is the output

Jimmy801_0-1616768669875.png

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

1 REPLY 1
Jimmy801
Community Champion
Community Champion

Hello @BlueNote 

 

try this solution. It groups first the data by name... because I suppose you will have more then 1 name in your table. Afterwards I add a Index column to the grouped table and add new column that creates a list of dates of every month till the next event. If no event is found, the last month is used. Here the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lHySszTNTIAMgx1FfxSyxU8MotSlWJ1UNQ4lqZD1BjpKnjlJyk4ZyTmpQNVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Date Request Filled" = _t, #"Reason Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Request Filled", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name"}, {{"AllRows", each 
    let 
        AddIndexAndSort = Table.AddIndexColumn(Table.Sort(_, {{"Date Request Filled", Order.Ascending}}), "Index", 1,1),
        AddListWithDates = Table.AddColumn
        (
            AddIndexAndSort,
            "Date Request Filled New",
            (add)=> let 
                GetEndDate = try Table.SelectRows(AddIndexAndSort, each [Index]= add[Index]+1)[Date Request Filled]{0} otherwise Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())),
                GetListOfDates = List.Transform(List.Numbers(0,((Date.Year(GetEndDate)-Date.Year(add[Date Request Filled]))*12)+ (Date.Month(GetEndDate)-Date.Month(add[Date Request Filled])) ,1), each Date.AddMonths(add[Date Request Filled], _))
            in 
             GetListOfDates
        )
        
        
    in 
     AddListWithDates }}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date Request Filled", "Reason Code", "Date Request Filled New"}, {"Date Request Filled", "Reason Code", "Date Request Filled New"}),
    #"Expanded Date Request Filled New" = Table.ExpandListColumn(#"Expanded AllRows", "Date Request Filled New")
in
    #"Expanded Date Request Filled New"

this is the output

Jimmy801_0-1616768669875.png

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors