Power Query sequence numbers

sadavies2906

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

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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 ?
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top