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

[Dave Clark]

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

 

[AliGW]

The latter. What perceived benefit do you see by doing this in PQ?

 

[Dave Clark]

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

 

[Ron Coderre]

Well....If you want to create the data in Power Query,
Here's one way:

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:

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?

 

[Ron Coderre]

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.

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?

 

[Dave Clark]

Thanks, Ron. I'll try this and report back.

 

[horseyride]

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

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"