Forum Discussion

Matty's avatar
Matty
Helper II
8 years ago
Solved

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

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_IndexStart_Date
109/04/2018
216/04/2018
323/04/2018
430/04/2018

 

And I want it to look as follows:

 

Week_IndexStart_Date
002/04/2018
109/04/2018
216/04/2018
323/04/2018
430/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

  • 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

2 Replies

  • 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's avatar
      Matty
      Helper II

      Hi Ninter,

       

      Thanks for that - it worked as desired.

       

      Cheers,

       

      Matty