Adding Extra Virtual Rows to a Table

[Anonymous]

Hi Guys,

Hope you all are doing great.

I think what I’m about to ask should be a bit complicated, but I count on your excellent knowledge of power query.

I’ve that table at the bottom. What I wish to accomplish with the power query to add 10 extra rows, extending the column: Date, so the series would be from 8/1/2021 to 8/15/2021, but this occurs only in case column: Migrant value is: Yes, taking in account that:

1. The row to repeat is the latest entry of this particular name (meaning, that we will need to add 10 extra rows for the First Person only, not the Second Person, and the new rows will have dates from 8/6/2021 till 8/15/2021 and the age in these extra columns would be: 46, not 45, and the job is: Professor, not Teacher).

2. The newly added rows will need to have some of the columns empty, such as column: Country, column: Married and column: Having Children, so, the newly added rows should have the content of column: Name and column: Age and column: Job only.

   Is it some thing that could be done? I think yes, but how? I’m not quite sure.

   Current dataset:

   

   Desired output:

   

[wdx223_Daniel]

NewStep=Table.Combine(Table.Group(PreviousStep,"Name",{"n",each let a=Table.Last(_),b=Table.FromRecords(List.Transform({1..10},each a&[Age=a[Age]+1,Job="Professor",Country=null,Married=null,#"Having Children"=null,Date=a[Date]+Duration.From(_)])) in if a[Migrant]="Yes" then _&b else _})[n])

[Anonymous]

Well, problem is with the hardcoding, the job won't be professor alway, the age won't be 1 year over the latest record always, I need something more dynamic to get me the latest record linked to this name, is this something possible, you think?