Forum Discussion
Matty
8 years agoHelper II
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_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
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
- InterkoubessSolution Sage
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
- MattyHelper II
Hi Ninter,
Thanks for that - it worked as desired.
Cheers,
Matty