Adding a incremental column based on the number of item on each order

[Anonymous]

HI Everyone,

I'm trying to add a column who can take in count the number of item that I have on each Order. If I have 3 orders. Let say the first one have 4 line,  the second one 1 line and the third one 3 line. I need a column who will give me a line No. with an incremental of 1000. So for the first order, I will see 1000, 2000, 3000, 4000.

Thanks for your help.

Eric B.

[edhans · Accepted solution]

You can do this in Power Query pretty easily by adding an index column to a nested table. See the M code below, and a link to a sample file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", type text}, {"TYPE", type text}, {"No.", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"No"}, {{"All Rows", each _, type table [No=text, TYPE=text, #"No."=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All Rows], "Index", 1000, 1000)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TYPE", "No.", "Index"}, {"TYPE", "No.", "Index"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"No", "TYPE", "No.", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"TYPE", type text}, {"No.", type text}, {"Index", Int64.Type}})
in
    #"Changed Type1"

File Link.

What it is doing is:

1. Grouping by your order number. It puts all other rows in an All Rows column.
2. I then add an index column to the nested table using Table.AddIndexColumn([All Rows], "Index", 1000, 1000). That adds an index starting at 1,000 and incrementing at 1,000 each time. It stop by default at the end of the table, which is limited to just that order number.
3. I then expand all of the columns from the nested table that i need, which is all but the first, which exists in the parent table.
4. Then I remove the columns I don't need, and reset the data types. Data types are lost when using nested tables.

[Nathaniel_C]

Hi @Anonymous ,

A lot of work, but interesting. I believe this is what you are looking as an output. Used my own dummy table. Will send you the pbix to look over. If it is satisfactory we will post it. Did add a index.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous , @edhans ,
Doing it in DAX as a measure. So @edhans will work for an Excel column.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Incrementer = 
VAR _maxItemNO =
    MAX ( 'ITEM'[NO] )       //Get current Item[NO]

VAR _presentindex1 =
    MAX ( 'ITEM'[Index 1] )    //Get current location on Item[Index 1] essentially row number

VAR _count =
    CALCULATE (
        COUNTROWS ( 'ITEM' ),
        FILTER ( ALLEXCEPT ( 'ITEM', 'ITEM'[NO] ), MAX ( 'ITEM'[NO] ) = _maxItemNO ) //Count how many of this Item [NO] there are
    )
VAR _Maxrow =
    CALCULATE (
        MAX ( 'ITEM'[Index 1] ),
        'ITEM'[NO] = _maxItemNO,
        ALLEXCEPT ( 'ITEM', 'ITEM'[NO] )                    //Find the maximum row for this Item [NO]
    )
VAR _diff = _Maxrow - _count                        //Subtract the count of items from the highest row number to find the difference

VAR _final = ( _presentindex1 - _diff ) * 1000      //Subtract the difference from the current row number and multiply by 1000

RETURN
    _final

[edhans]

---

@Nathaniel_C wrote:

Hi @Anonymous , @edhans ,
Doing it in DAX as a measure. So @edhans will work for an Excel column.

---

My method was not an Excel column. It is a custom column in Power Query. I just displayed the results in Excel for simplicity sake. Adding manual data to Power BI for examples is a pain and a half.

That M code/logic can be reused in Power BI Desktop.

[Nathaniel_C]

@edhans ,  @Anonymous ,

Yes.  However that is not what I wrote. 

---

@edhans wrote:

---

---

My method was not an Excel column. 

---

What I wrote was,"So @edhans will work for an Excel column." Work meaning is appropriate for an Excel column.
Sorry for the confusion.

Nathaniel

[edhans]

It isn't an Excel column. It has nothing at all to do with Excel. 

Here it is in Power Query

And here it is in Table View in Power BI loaded into the data model.

There is no Excel column. Period. You can do this in Power Query, which is available both in Excel, and Power BI, but an Excel column would be a function like =VLOOKUP() or something in a spreadsheet. There is no spreadsheet in this process.