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

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126

ADVERTISEMENT

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

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

Well-known Member
Joined
Oct 24, 2015
Messages
7,126

ADVERTISEMENT

You are welcome
if it works for you , you can hit Like (y) 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
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
or just merge by columns Job Number. you need to test it what will be better
 

sadavies2906

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

Watch MrExcel Video

Forum statistics

Threads
1,114,486
Messages
5,548,350
Members
410,828
Latest member
A9Bosv3
Top