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]

The xls files are a dump straight out of our ERP system that our IT department do automatically on a daily basis so unfortunately I don't have any input to get this changed, anything i create tend to be either xlsx or xlsm

 

[sandy666]

sure
what about my question (post#20)

 

[sandy666]

after open saved file on your computer refresh all tables

for sequence column change type data to number if you want

your file

 

[sadavies2906]

sure
what about my question (post#20)

Sorry sandy666 only just seen this, yes i was trying to get it to work on "POST VENDOR" tab but will probably migrate it to "PRE VENDOR" and "VENDOR" once i get my head around it

 

[sandy666]

Sorry sandy666 only just seen this, yes i was trying to get it to work on "POST VENDOR" tab but will probably migrate it to "PRE VENDOR" and "VENDOR" once i get my head around it

too late, I did it before you answer

 

[sadavies2906]

Thanks sandy666 it works like a charm and i've managed to migrate it to the other tabs i needed it to as well.

Another quick question hopefully, in the "PRE VENDOR" tab I end up with a column Q that is empty apart from a substituted "-" that I built into the M CODE.

What I'm doing is manually copying the formula in cell AC2 on the same tab into this column and this essential returns a "YES" or "NO" based on the information in the "DAILY SHORTAGE" tab.

With M CODE could I get the SHORTAGE column to be "=IFNA(IF(MATCH([@[Job Number]],DAILY_DOWNLOAD[Job Number],0),"YES","NO"),"NO")" instead of the "-" and it will still work as a formula?

Thanks for all the help so far

Regards
Stephen

 

[sandy666]

You are welcome
if it works for you , you can hit Like in the posts which helped you

are you trying mix M with worksheet formula? this is totally another logic.
maybe merge PRE VENDOR with DAILY SHORTAGE by index then remove all columns except one you need and create IF (Conditional Column). why you are searching through whole table DAILY SHORTAGE?
btw. you cannot change data type to text in the last created sequence column because column contain text, so if you change it you'll get error

 

[sandy666]

or just merge by columns Job Number. you need to test it what will be better

 

[sadavies2906]

When the Daily Download query runs i filter on a column called "NEXT ASSY PLANNER CODE" and just look for anything that contains "MESC", "MESCOV", "OSPESC" and "OSPESCOV" so this drastically reduces the size of the file.

This essentially gives me a table of "JOB NUMBERS" that contain shortages and the details of those shortages.

The INDEX, MATCH formula I manually add just looks to see if the "JOB NUMBER" on the "PRE VENDOR" tab is in the "DAILY SHORTAGE" table and returns the "yes" or "no"

 

[sadavies2906]

I will give your suggestion of a merge a try and see what it looks like