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

[dewandi]

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.

[#2]

@dewandi 

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.

[#3]

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.
Thanks.

Newbie in Power Query

[#4]

@dewandi 

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

[#5]

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

[#6]

@dewandi 

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

Correct?