Results 1 to 7 of 7

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

  1. #1
    Neophyte Dave Clark's Avatar
    Join Date
    Jan 2020
    Location
    Mono, Ontario, Canada (near Toronto)
    Posts
    3
    Articles
    0
    Excel Version
    Office 365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    The latter. What perceived benefit do you see by doing this in PQ?
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte Dave Clark's Avatar
    Join Date
    Jan 2020
    Location
    Mono, Ontario, Canada (near Toronto)
    Posts
    3
    Articles
    0
    Excel Version
    Office 365
    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

  4. #4
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    110
    Articles
    0
    Excel Version
    2013, 2016, O365
    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?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  5. #5
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    110
    Articles
    0
    Excel Version
    2013, 2016, O365
    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?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  6. #6
    Neophyte Dave Clark's Avatar
    Join Date
    Jan 2020
    Location
    Mono, Ontario, Canada (near Toronto)
    Posts
    3
    Articles
    0
    Excel Version
    Office 365
    Thanks, Ron. I'll try this and report back.

  7. #7
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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 by horseyride; 2020-01-30 at 01:18 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •