Power Query sequence numbers

[sadavies2906]

Hi, I have the following Power Query running that eventually creates a workbook table with a column "WTL_SEQ_NEW"

let
Source = Excel.Workbook(File.Contents("N:\ManufacturingOps\Spares Overdue Weekly Report\2020 WORK TO LISTS\DATA FILES\SDD_ESC_Resource Requirement Report UK V2.xls"), null, true),
#"ESC Build" = Source{[Name="ESC Build"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"ESC Build", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start Date", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Hours", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Location] = "POST VENDOR"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Start Date", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,"-",Replacer.ReplaceValue,{"Destination"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"-",Replacer.ReplaceValue,{"Po Number"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "WTL_SEQ_NEW", each Text.End([Week],2)),
#"Sorted Rows1" = Table.Sort(#"Added Custom",{{"WTL_SEQ_NEW", Order.Ascending}, {"Assembly Item", Order.Ascending}, {"Job Number", Order.Ascending}})
in
#"Sorted Rows1"

The result is shown below as "CURRENT" but i would like to automatically create a sequence similar to "REQUIRED" where it increments by +1

Regards
Stephen

 

[sadavies2906]

Sorry sandy666, i should have included the whole file, what you have done works perfectly but only keeps the single column "Result"

The sequence needs to be applied so i can use it within the output table below later on so all columns need to be available for display/output

 

[sandy666]

use XL2BB not a picture
or post a link to the shared excel file using onedrive, googledrive, dropbox or any similar

Question: the sequence should be a number or text ?

 

[sadavies2906]

Sorry sandy666 I'm happy with either but a number would probably be easier to use

 

[sandy666]

Sorry sandy666 I'm happy with either but a number would probably be easier to use

sure
I have to go and will be a bit later but still waiting for shared excel file

 

[sadavies2906]

ESC Work to List - DAILY_2020_07_02.xlsm

drive.google.com

I think this will work, never really used these before

 

[sandy666]

there is no source data PLANNING.SDD_CMS_DAILY.xls so I can do nothing

 

[sadavies2906]

SDD_ESC_Resource Requirement Report UK V2.xls

drive.google.com

PLANNING.SDD_CMS_DAILY.xls

drive.google.com

 

[sandy666]

[ removed ]

 

[sadavies2906]

Hi sandy666 are the file formats i sent through usable, i did them on google drive

 

[sandy666]

data is data so no problem
I wonder only WHY you are still using very old excel format: xls

to be sure: we are talking about POST VENDOR tab ?