Create add column from n record of a field - Power Query Excel

Occasional Contributor

Hi guys,

I do Power Query Excel (ver. 365) and I want to add new column from my table as follow:


1. I want to add new column (Date) from value of Column2 record no 2 (9/23/2020). 

2. Another one, I want to make a group based on Column2.

How to make a script for that issue.


The final table like this:



Thank you.





5 Replies


You may add Date as custom column


assuming previous step is Source, or use correct one.

Didn't catch what is the logic for another column.

Thank you @BaklanSergei Baklan.
Solve for no 1. I use the file name of the report, transform it to create the date.
For issue no 2. I try to group it, so i can find total Recond and Total Non Recond.

Newbie in Power Query


Okay, but is the logic, how Excel knows that Excel shall be Non Recond  but DMA Exon - Recond?

The key point is I just want to sum the above part and the below part (without breakdown).


So, to formalize a bit:

- you ALWAYS have only two blocks of data

- first block starts after empty row down from amount in Column3

- second block is after empty row bottom after first block

- last row for the first block in Column3 ALWAYS has total

- we NEVER have total for the second block