Forum Discussion
Product Serial number table based on input
Hi Anonymous ,
This is better to be solved in Power Query however I have a doubt that is you start and end serial have different number of lines whast is the value you want in the end example in the first line the first serials have 469 lines (DD1A1923000996 - DD1A1923001465) and in the second part they have 129 lines (1922000011 - 1922000140)
Hi All,
WIth help of one of friend i manage to find below code which is genarating serial numbers for me however in 13% cases it is throwing error
"An error occurred in the ‘WithFactory Code’ query. Expression.Error: The number is out of range of a 32 bit integer value.
Details:
19400340024"...Sharing code as well as error screen, Kindly guide me on the same ..
let
Source = Excel.Workbook(File.Contents("C:\Users\APCP\OneDrive - \BC)\Dashboard\Capture As It Is.xlsx"), null, true),
#"WithFactory Code_Sheet" = Source{[Item="WithFactory Code",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"WithFactory Code_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date ", type any}, {"Month", type text}, {"Model No.", type text}, {"From", type text}, {"To", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"From", "Start"}, {"To", "End"}}),
StartNo = Table.AddColumn(#"Renamed Columns", "StartNo", each List.Last(Text.SplitAny([Start],"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))),
EndNo = Table.AddColumn(StartNo, "EndNo", each List.Last(Text.SplitAny([End],"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))),
#"Added Custom" = Table.AddColumn(EndNo, "Prefix", each Text.Split([Start],[StartNo]){0}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {Number.From([StartNo])..Number.From([EndNo])})
in
#"Added Custom1"Sanket
Hi Anonymous ,
Does the ID's that have letters always have the same number of letters? In this case I see 4 letters at the start.
Hi There,
These letters are not fixed, it varies based on production location.
Sanket