Insert row with reference to an existing row - Power Query...

[Matty]

Hi Team,

Looking for some help inserting a new row (record) to my 2 column table based on the minimum date in one of the columns.

Here's my table: 

Week_Index	Start_Date
1	09/04/2018
2	16/04/2018
3	23/04/2018
4	30/04/2018

And I want it to look as follows:

Week_Index	Start_Date
0	02/04/2018
1	09/04/2018
2	16/04/2018
3	23/04/2018
4	30/04/2018

Where a new record has been inserted with 'Week_Index' = 0 and 'Start_Date' = min of all existing dates - 7 days.

I have the following code, but the syntax is not quite right:

Custom1 = Table.InsertRows(#"Renamed Columns",2, { [Week_Index = 0, Start_Date = List.Min([Start_Date]-7)] })

Can anyone help, please?

Thanks,

Matty

[Interkoubess · Accepted solution]

Hi @Matty,

Please change your formula by the following one:

Custom1=Table.InsertRows(#"Renamed Columns",2, { [Week_Index = 0, Start_Date = List.Min(#"Renamed Columns"[Start_Date])+#duration(-7,0,0,0))] })

Hope it helps....

Ninter

[Matty]

Hi Ninter,

Thanks for that - it worked as desired.

Cheers,

Matty