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

%3CLINGO-SUB%20id%3D%22lingo-sub-3098911%22%20slang%3D%22en-US%22%3ECreate%20add%20column%20from%20n%20record%20of%20a%20field%20-%20Power%20Query%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3098911%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3EI%20do%20Power%20Query%20Excel%20(ver.%20365)%20and%20I%20want%20to%20add%20new%20column%20from%20my%20table%20as%20follow%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dewandi_0-1643724840621.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344161i5206FF9EC7C33486%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dewandi_0-1643724840621.png%22%20alt%3D%22dewandi_0-1643724840621.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E1.%20I%20want%20to%20add%20new%20column%20(Date)%20from%20value%20of%20Column2%20record%20no%202%20(9%2F23%2F2020).%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Another%20one%2C%20I%20want%20to%20make%20a%20group%20based%20on%20Column2.%3C%2FP%3E%3CP%3EHow%20to%20make%20a%20script%20for%20that%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20final%20table%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dewandi_1-1643725587993.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344163iDA3DFFF104CB5ABD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dewandi_1-1643725587993.png%22%20alt%3D%22dewandi_1-1643725587993.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3098911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3101434%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20add%20column%20from%20n%20record%20of%20a%20field%20-%20Power%20Query%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3101434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1288356%22%20target%3D%22_blank%22%3E%40dewandi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20Date%20as%20custom%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20698px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344342i8C5DEB653C26E49D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eassuming%20previous%20step%20is%20Source%2C%20or%20use%20correct%20one.%3C%2FP%3E%0A%3CP%3EDidn't%20catch%20what%20is%20the%20logic%20for%20another%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3105442%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20add%20column%20from%20n%20record%20of%20a%20field%20-%20Power%20Query%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3105442%22%20slang%3D%22en-US%22%3EThank%20you%20%40BaklanSergei%20Baklan.%3CBR%20%2F%3ESolve%20for%20no%201.%20I%20use%20the%20file%20name%20of%20the%20report%2C%20transform%20it%20to%20create%20the%20date.%3CBR%20%2F%3EFor%20issue%20no%202.%20I%20try%20to%20group%20it%2C%20so%20i%20can%20find%20total%20Recond%20and%20Total%20Non%20Recond.%3CBR%20%2F%3EThanks.%3CBR%20%2F%3E%3CBR%20%2F%3ENewbie%20in%20Power%20Query%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi guys,

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

dewandi_0-1643724840621.png

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:

dewandi_1-1643725587993.png

 

Thank you.

 

 

 

 

5 Replies

@dewandi 

You may add Date as custom column

image.png

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

Newbie in Power Query

@dewandi 

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

@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?