Product Serial number table based on input

[Anonymous]

Hi There,

I have two types of product serial number series ( 1> Alphanumeric 2> Numeric) with Starting and ending details in power BI tables.

I want to generate a complete set of serial number tables in power Bi, could you please help me with the DAX formula.

Example input table

Month

Model No.

Start

End

Month

Model No.

Start Sr. No.

End Sr. No.

June

CD1000

DD1A1923000996

DD1A1923001465

June

BB1245

1922000011

1922000140

June

DD1230

DD1A1925001466

DD1A1925001510

June

FD2321

1922000212

1922000361

June

CD1000

DD1A1924004951

DD1A1924005230

June

BB1245

1922000141

1922000211

June

DD1230

DD1A1924005231

DD1A1924005565

June

FD2321

1922000412

1922000660

Example output table

Month		Model No.		Serial			Month		Model No.		Serial
June		BB1245		1922000011			June		CD1000		DD1A1923000996
June		BB1245		1922000012			June		CD1000		DD1A1923000997
June		BB1245		1922000013			June		CD1000		DD1A1923000998
June		BB1245		1922000014			June		CD1000		DD1A1923000999
June		BB1245		1922000015			June		CD1000		DD1A1923001000

Regards,

Sanket 

[MFelix]

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)

[Anonymous]

Hi There,

These letters are not fixed, it varies based on production location.

Sanket 

[MFelix]

HI @Anonymous ,

Not talking about the lettter but the number of letters does it have different number of characters?

[Anonymous]

Hi @MFelix,

Number of characters will be same.

Sanket

[MFelix]

Hi @Anonymous ,

Sorry for another question but looking at your query and to the data you provided is the first table you provided a single table with repeating columns or did you paste two examples one in front of the other?

[Anonymous]

@MFelix,

Regarding "Example input table" these are two different tables. One has alphanumeric values and the other has only numeric values, We have these two different styles of serial numbers, I want to generate both types of serial numbers.

If possible (if Powe query has the ability) I would love to merge (append)  both the tables instead of having two different tables. 

Thanks & Regards,

Sanket 

[Anonymous]

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 

[MFelix]

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.