Wire numbers - incrementing the values in a column by a value in each row

D

Dave Clark

New member
Joined
Jan 23, 2020
Messages
4
Reaction score
0
Points
0
Location
Mono, Ontario, Canada (near Toronto)
Excel Version(s)
Office 365
Hi.
I have a query that produces rows of data. Each row is a connection.
There's a column called ID and a column called Number of Wires (for that connection). ​
​
I want to create two columns that show serialized wire numbers for each connection based on the number of wires in each connection. This is pretty straightforward in Excel. How do I do this in Power Query? Here's what I'm trying to do:

ID Number of Wires Wire # Start Wire # End
A 4 00001 00004
B 6 00005 00010
C 0
D 5 00011 00015
etc.

Should I try to make this work in a query or just make a table in Excel and query it?

Thanks,

Dave
 
The latter. What perceived benefit do you see by doing this in PQ?
 
Hi Ali
I'm hoping that the PQ way might make it faster and more stable. I already have a query with the initial data - I imagined that I could add a couple of columns and be off to the races.
Dave
 
Well....If you want to create the data in Power Query,
Here's one way:

Code: 
let
    Source = Table1,

    // Add a sequential index to the entire table
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1),

    // Add the start sequence values
    AddListStart = Table.AddColumn(AddIndex, "Wire # Start", 
        each Number.ToText(
        if [Number_of_Wires] = 0
            then 0
        else if [Index] = 1
            then 1 
            else List.Sum(
            List.Range(
                AddIndex[Number_of_Wires], 
                0, 
                List.PositionOf(AddIndex[Number_of_Wires],[Number_of_Wires])
            )
        )+1, "00000"), type text),

    // Add the end sequence values
    AddListEnd = Table.AddColumn(AddListStart, "Wire # End", 
        each Number.ToText(
        if [Number_of_Wires] = 0
            then 0
        else List.Sum(
            List.Range(
                AddIndex[Number_of_Wires], 
                0, 
                List.PositionOf(AddIndex[Number_of_Wires],[Number_of_Wires])+1
            )
        ), "00000"), type text)
in
    AddListEnd

These are the results:
Code: 
ID       Number_of_Wires       Index       Wire # Start       Wire # End
A                      4       1                   00001           00004
B                      6       2                   00005           00010
C                      0       3                   00000           00000
D                      5       4                   00011           00015

Is that something you can work with?
 
Sometimes I wonder where my head is at!
This M-Code is more concise. Previous post, I started with the ending value. This time I started with the starting value and just needed to add the item count to it.
Code: 
let
    Source = Table1,

    // Add a sequential index to the entire table
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1),

    // Add Start number col
    AddListStart = Table.AddColumn(AddIndex, "Wire # Start", 
        each Number.ToText(
        if [Number_of_Wires] = 0
            then 0
        else if [Index] = 1
            then 1 
            else List.Sum(
            List.Range(
                AddIndex[Number_of_Wires], 
                0, 
                List.PositionOf(AddIndex[Number_of_Wires],[Number_of_Wires])
            )
        )+1, "00000"), type text),
    AddListEnd = Table.AddColumn(AddListStart, "Custom", each Number.ToText(List.Max({Number.From([#"Wire # Start"])+[Number_of_Wires] - 1,0}),"00000"), type text)

in
    AddListEnd

Does that help?
 
Here's my version; similar theory, by step rather than in one shot

Add Index
Use index to create cumulative sum
Two formulas to add serials
Remove index

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Cum Total" = Table.AddColumn(#"Added Index", "CumTotal", each List.Sum(List.FirstN(#"Added Index"[Number of Wires],[Index]+1))),
    #"Added Custom" = Table.AddColumn(#"Added Cum Total", "Serial Start", each if [Index]=0 then "00000001" else if [Number of Wires] = 0 then "" else Text.PadStart(Text.From([CumTotal]-[Number of Wires]+1),8,"0")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Serial End", each if [Number of Wires] = 0 then "" else Text.PadStart(Text.From([CumTotal]),8,"0")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"})
in
#"Removed Columns"
 
Last edited:
You must log in or register to reply here.
Back
Top