Forum Discussion
Product Serial number table based on input
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
8 Replies
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 Anonymous ,
Not talking about the lettter but the number of letters does it have different number of characters?
