• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Product Serial number table based on input table

S

Sanket Katdare

New Member
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
 
You haven't had a response to this and I'm not conversant enough in DAX to do this but I submit the following Power Query solution if that might be in any way useful?
In the attached are your tables (in blue) and a result table (in green), which is a Power Query output from whichever table is called Table1. At the moment, I've left the left blue table as Table1. If you rename the second blue table to Table1 (after remaming the first to something else), then go to the green table and right-click and choose Refresh, the result table should update.
Assumptions:
1. Only upper case alpha characters are present in your serial numbers.
2. The prefix to the numbers is the same in Start and End (because I've only taken the prefix from Start).
 

Attachments

  • Chandoo47747.xlsx
    37.7 KB · Views: 2
p45cal said:
You haven't had a response to this and I'm not conversant enough in DAX to do this but I submit the following Power Query solution if that might be in any way useful?
In the attached are your tables (in blue) and a result table (in green), which is a Power Query output from whichever table is called Table1. At the moment, I've left the left blue table as Table1. If you rename the second blue table to Table1 (after remaming the first to something else), then go to the green table and right-click and choose Refresh, the result table should update.
Assumptions:
1. Only upper case alpha characters are present in your serial numbers.
2. The prefix to the numbers is the same in Start and End (because I've only taken the prefix from Start).
Click to expand...
Hi There,

Thanks a lot, This solution works perfectly to me.

Sanket
 
p45cal said:
You haven't had a response to this and I'm not conversant enough in DAX to do this but I submit the following Power Query solution if that might be in any way useful?
In the attached are your tables (in blue) and a result table (in green), which is a Power Query output from whichever table is called Table1. At the moment, I've left the left blue table as Table1. If you rename the second blue table to Table1 (after remaming the first to something else), then go to the green table and right-click and choose Refresh, the result table should update.
Assumptions:
1. Only upper case alpha characters are present in your serial numbers.
2. The prefix to the numbers is the same in Start and End (because I've only taken the prefix from Start).
Click to expand...

Hi There,

The process is generating 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 ..

78158


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"
 
So you got some fairly long serial numbers where the last part goes above the 32 bit integer value which is required for the 2 numbers in the {n1..n2} notation.
So I went for List.Generate.
In the attached there are quite a few changes to the query, including the inclusion of a function:
fnSerialGen=(strt,end)=> List.Generate(() => strt, each _ <= end, each _ + 1)
and the addition of a step to convert 2 columns to whole numbers:
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"StartNo", Int64.Type}, {"EndNo", Int64.Type}}),
Hopefully you won't go over the 64 bit integer value (but we can work around that if necessary).
 

Attachments

  • Chandoo47747b.xlsx
    38.7 KB · Views: 4
You must log in or register to reply here.
Back
Top