none
Creating Unique Group Numbers for record sets using Power Query RRS feed

  • Question

  • Hello,

    I'm trying to associate a sequential group of records based on a unique number per group of rows. The beginning of each record set has the first records value "POS" in the "pos" column. I tried creating a indexed column and creating additional custom column which sees if "pos" column has a value of "POS" it copies it to the "Custom" column. If not, it looks a the Index -1 row and copies the value in the "Custom" column to the current row of the "Custom".

    = Table.AddColumn(#"Added Index", "Custom", each if [pos] = "POS" then [id] else #"Added Index"{[Index]-1}[Custom])

    However. it errors.  The error says  " Expression.Error: The field 'Custom' of the record wasn't found."

    id pos origin dest truck Trailer z_timestamp created_at Index Custom
    440656 POS TTT HHH 10808 307 2019-03-09 03/09/2019 00:32:03 1 440656
    440657 :D2 TTT HHH 10808 307 2019-03-09 03/09/2019 00:32:03 2 error
    440658 :D6 TTT HHH 10808 307 2019-03-09 03/09/2019 00:32:04 3 error
    440659 :6L TTT HHH 10808 307 2019-03-09 03/09/2019 00:32:10 4 error
    440660 :7L TTT HHH 10808 307 2019-03-09 03/09/2019 00:32:10 5 error
    440661 POS AAA HHH 13501 1327 2019-03-09 03/09/2019 01:25:06 6 440661
    440662 :A1 AAA HHH 13501 1327 2019-03-09 03/09/2019 01:25:06 7 error
    440663 :2L AAA HHH 13501 1327 2019-03-09 03/09/2019 01:25:06 8 error
    440664 :2R AAA HHH 13501 1327 2019-03-09 03/09/2019 01:25:10 9 error
    440665 :3L AAA HHH 13501 1327 2019-03-09 03/09/2019 01:25:10 10 error
    440666 POS BBB JJJ 13601 1399 2019-03-09 03/09/2019 03:09:05 11 440666
    440667 :A1 BBB JJJ 13601 1399 2019-03-09 03/09/2019 03:09:05 12 error
    440668 :2L BBB JJJ 13601 1399 2019-03-09 03/09/2019 03:09:05 13 error
    440669 :2R BBB KKK 13601 1399 2019-03-09 03/09/2019 03:09:05 14 error
    440670 :3L BBB JJJ 13601 1399 2019-03-09 03/09/2019 03:09:05 15 error
    440671 :3R BBB KKK 13601 1399 2019-03-09 03/09/2019 03:09:05 16 error
    440672 POS CCC JJJ 13533 1439 2019-03-09 03/09/2019 03:12:05 17 440672
    440673 :A1 CCC JJJ 13533 1439 2019-03-09 03/09/2019 03:12:05 18 error
    440674 :2L CCC JJJ 13533 1439 2019-03-09 03/09/2019 03:12:05 19 error
    440675 :2R CCC JJJ 13533 1439 2019-03-09 03/09/2019 03:12:05 20 error
    440676 :3L CCC JJJ 13533 1439 2019-03-09 03/10/2019 03:12:05 21 error
    440677 :3R CCC JJJ 13533 1439 2019-03-09 03/10/2019 03:12:05 22 error

    Ideas

    Thanks for your help

    Monday, March 25, 2019 4:56 PM

Answers

  • That was the answer, thanks so much.  It was a Duh! moment :^)  I'm new to Power Query for about a month.  

    Thanks again

    • Marked as answer by GregS25 Tuesday, March 26, 2019 1:36 PM
    Tuesday, March 26, 2019 1:36 PM

All replies

  • #"Added Index"{[Index]-1}[Custom] is referring to a column named "Custom" that does not exist in the #"Added Index" table, hence the error. 

    Plus, you cannot refer back to a column that you are defining (whose values haven't been evaluated yet). That would be equivalent to a circular reference. The 'else' expression must either be a literal, a value from an existing column, or a formula using values from existing columns.

    Monday, March 25, 2019 5:29 PM
  • Thanks for your reply.

    Is there a way to change the value of a literal. I have been able to use a variable in a IF statement to define the value of a row column. But I have not been successful in changing the value of a variable in a IF statement. Can this be done? If so, please provide an example.

    Cheers,

    Monday, March 25, 2019 8:19 PM
  • No. In functional languages (including M), you cannot change the value of a variable. You can create a new variable based on an existing one.

    Is it possible for you  to provide a table of your required output, based on a input table with all columns and column names?

    Monday, March 25, 2019 8:42 PM
  • The beginning of each record set is identified where the value of "POS" in the "pos" column.  The value of the "Key" is the "id" of the "POS" row. The "Key" value should not change until the next "POS" row.

    Here is the input:

    id pos origin dest truck Trailer z_timestamp created_at Key
    440656 POS TTT HHH 10808 307 3/9/2019 3/9/2019 00:32 0
    440657 :D2 TTT HHH 10808 307 3/9/2019 3/9/2019 00:32 0
    440658 :D6 TTT HHH 10808 307 3/9/2019 3/9/2019 00:32 0
    440661 POS AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 0
    440662 :A1 AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 0
    440663 :2L AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 0
    440664 :2R AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 0
    440666 POS BBB JJJ 13601 1399 3/9/2019 3/9/2019 03:09 0
    440667 :A1 BBB JJJ 13601 1399 3/9/2019 3/9/2019 03:09 0
    440669 :2R BBB KKK 13601 1399 3/9/2019 3/9/2019 03:09 0
    440672 POS CCC JJJ 13533 1439 3/9/2019 3/9/2019 03:12 0
    440673 :A1 CCC JJJ 13533 1439 3/9/2019 3/9/2019 03:12 0
    440674 :2L CCC JJJ 13533 1439 3/9/2019 3/9/2019 03:12 0

    output:

    id pos origin dest truck Trailer z_timestamp created_at Key
    440656 POS TTT HHH 10808 307 3/9/2019 3/9/2019 00:32 440656
    440657 :D2 TTT HHH 10808 307 3/9/2019 3/9/2019 00:32 440656
    440658 :D6 TTT HHH 10808 307 3/9/2019 3/9/2019 00:32 440656
    440661 POS AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 440661
    440662 :A1 AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 440661
    440663 :2L AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 440661
    440664 :2R AAA HHH 13501 1327 3/9/2019 3/9/2019 01:25 440661
    440666 POS BBB JJJ 13601 1399 3/9/2019 3/9/2019 03:09 440666
    440667 :A1 BBB JJJ 13601 1399 3/9/2019 3/9/2019 03:09 440666
    440669 :2R BBB KKK 13601 1399 3/9/2019 3/9/2019 03:09 440666
    440672 POS CCC JJJ 13533 1439 3/9/2019 3/9/2019 03:12 440672
    440673 :A1 CCC JJJ 13533 1439 3/9/2019 3/9/2019 03:12 440672
    440674 :2L CCC JJJ 13533 1439 3/9/2019 3/9/2019 03:12 440672

    Cheers


    • Edited by GregS25 Monday, March 25, 2019 9:22 PM
    Monday, March 25, 2019 9:16 PM
  • Hi Greg,

    Try the following. You didn't provide column names for your table, so the solution uses Column1 to Column8. Change to actual names accordingly.

    let
        source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        removedColumns = Table.RemoveColumns(source,{"Column8"}),
        addedCustom = Table.FillDown(Table.AddColumn(
            removedColumns, 
            "Column8", 
            each if Text.EndsWith([Column1], "POS") then [Column1] else null
            ), {"Column8"}
        ),
        transformedColumn = Table.TransformColumns(addedCustom, {"Column8", each Text.BeforeDelimiter(_, "POS")})
    in
        transformedColumn



    Monday, March 25, 2019 9:53 PM
  • Thanks you for the supplied code.  I tried it, but did not seem to fill out the values in the new column. It's almost there. 

    The columns are id, pos, origin, dest, truck, trailer, z_timestamp, created_at and the new column is Key.

    Example: The blank fields in the Key column for rows with id= 440657 & 440658 should have the value 440656 fill in.

    id pos origin dest truck Trailer z_timestamp created_at Key
    440656 POS TTT HHH 10808 307 3/9/2019 00:00 3/9/2019 00:32 440656
    440657 :D2 TTT HHH 10808 307 3/9/2019 00:00 3/9/2019 00:32  
    440658 :D6 TTT HHH 10808 307 3/9/2019 00:00 3/9/2019 00:32  
    440661 POS AAA HHH 13501 1327 3/9/2019 00:00 3/9/2019 01:25 440661
    440662 :A1 AAA HHH 13501 1327 3/9/2019 00:00 3/9/2019 01:25  
    440663 :2L AAA HHH 13501 1327 3/9/2019 00:00 3/9/2019 01:25  
    440664 :2R AAA HHH 13501 1327 3/9/2019 00:00 3/9/2019 01:25  
    440666 POS BBB JJJ 13601 1399 3/9/2019 00:00 3/9/2019 03:09 440666
    440667 :A1 BBB JJJ 13601 1399 3/9/2019 00:00 3/9/2019 03:09  
    440669 :2R BBB KKK 13601 1399 3/9/2019 00:00 3/9/2019 03:09  
    440672 POS CCC JJJ 13533 1439 3/9/2019 00:00 3/9/2019 03:12 440672
    440673 :A1 CCC JJJ 13533 1439 3/9/2019 00:00 3/9/2019 03:12  
    440674 :2L CCC JJJ 13533 1439 3/9/2019 00:00 3/9/2019 03:12  

    Thanks



    • Edited by GregS25 Tuesday, March 26, 2019 1:20 AM Better table format
    Tuesday, March 26, 2019 12:54 AM
  • Excel 2010 to 2019 Power Query (aka Get & Transform)
    With FillDown(), same as Colin's approach.
    With generic data (no trucks) for everybody else.
    Used GroupID (key) to process data further.
    http://www.mediafire.com/file/ddc0j8tidca3nz4/03_25_19.xlsx/file
    http://www.mediafire.com/file/ngz5518k5x1rfxa/03_25_19.pdf/file

    Tuesday, March 26, 2019 1:50 AM
  • Thanks for the update. Didn't realize that 'id' and 'pos' were separate columns. Try the following:

    let
        source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        removedKeyColumn = Table.RemoveColumns(source,{"Key"}),
        addedKeyColumn = Table.AddColumn(removedKeyColumn, "Key", each if [pos] = "POS" then [id] else null),
        filledDownKeyColumn = Table.FillDown(addedKeyColumn,{"Key"})
    in
        filledDownKeyColumn

    It was easier to replace the existing Key column in the source with new Key column than it was to modify the existing one. One step could have been eliminated if the source didn't have an unnecessary Key column.


    Tuesday, March 26, 2019 1:55 AM
  • That was the answer, thanks so much.  It was a Duh! moment :^)  I'm new to Power Query for about a month.  

    Thanks again

    • Marked as answer by GregS25 Tuesday, March 26, 2019 1:36 PM
    Tuesday, March 26, 2019 1:36 PM