Power Query sequence numbers

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
44
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

1593678528907.png


Regards
Stephen
 

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
44
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

1593692679137.png
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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 ?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
there is no source data PLANNING.SDD_CMS_DAILY.xls so I can do nothing
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,487
Messages
5,548,351
Members
410,828
Latest member
A9Bosv3
Top