Adding conditional index based on changing field in Power Query M

[Anonymous]

Hi alll, 

i have the Product and date table below and i am trying to add an index column that increments everytime the product changes. is this possible in M ? 

thank you! 

Product	Date	Index
I	2018-05-12	1
I	2018-05-13	1
I	2018-05-14	1
A	2018-05-14	2
A	2018-05-14	2
I	2018-05-15	3
I	2018-05-15	3
I	2018-05-16	3
A	2018-05-17	4
I	2018-05-19	5
A	2018-05-20	6
A	2018-05-20	6
I	2018-05-21	7
A	2018-05-21	8
I	2018-05-21	9
I	2018-05-21	9
A	2018-05-22	10
I	2018-05-22	11
I	2018-05-22	11
A	2018-05-24	12
A	2018-05-25	12
I	2018-05-27	13
A	2018-05-28	14
I	2018-05-28	15
A	2018-05-29	16
A	2018-05-29	16
A	2018-05-30	16
I	2018-05-30	17
A	2018-06-02	18
I	2018-06-03	19
A	2018-06-04	20
I	2018-06-04	21
I	2018-06-06	21
A	2018-06-06	22
I	2018-06-07	23
A	2018-06-07	24
A	2018-06-07	24
A	2018-06-08	24
A	2018-06-08	24
I	2018-06-08	25

[Anonymous · Accepted solution]

---

@edhans wrote:

Yes. Here is what I have done.

1. Create an index that begins with 0 for the list.
2. Create a 2nd index that begis with 1.
3. Now, Join the table to itself (merge) connecting Index 0 to Index 1. This will make the merged table one row off from the source table, which will allow you to do your comparison.
4. Expand the [Product] field in the merged table.
5. Get rid of the null row in the 2nd [Product] field. It is null because your source table had a 0 in the index and nothing to merge with in the index that started with 1.
6. Sort the table by index0 to ensure it is in order.
7. Add a comparison column that is: 

   if [item_no]<>[item_no.1] then [Index.1] else null

8. If the product numbers are equal, then it will pull the new index1 field, else it will pull null. 
9. Now, in the comparson column (I've called it NewItem Index], do a Fill Down so the nulls will be replaced with the number above it.
10. Finally, add a column that does this:

    ([Index.1]-[NewItem Index]+1)

11. that will generate a column where the number will change with each new product item.
12. Delete all of the temporary index columns.

Here are the actual M code steps in my query. You cannot just drop these in since my columns and yours are probably different. I was doing the same with shipment numbers rather than product numbers. The step preceding the first step below was called #"Sorted Rows" and my deleting of the extra junk happened later in my query so that isn't shown either.

    #"Added Index0" = Table.AddIndexColumn(#"Sorted Rows", "Index.0", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index0", "Index.1", 1, 1),
    #"Merged with self for indexes" = Table.NestedJoin(#"Added Index1",{"Index.0"},#"Added Index1",{"Index.1"},"NewColumn",JoinKind.FullOuter),
    #"Expanded comparison ItemNo" = Table.ExpandTableColumn(#"Merged with self for indexes", "NewColumn", {"item_no"}, {"item_no.1"}),
    #"Filtered null item_no" = Table.SelectRows(#"Expanded comparison ItemNo", each ([item_no] <> null)),
    #"Sorted Index.0" = Table.Sort(#"Filtered null item_no",{{"Index.0", Order.Ascending}}),
    // If the item comparison doesn't match, then pull Index.1, else fill with null.
    #"Added NewItem Index" = Table.AddColumn(#"Sorted Index.0", "NewItem Index", each if [item_no]<>[item_no.1] then [Index.1] else null),
    #"Filled Down to replace nulls in NewItem Index" = Table.FillDown(#"Added NewItem Index",{"NewItem Index"}),
    #"Added ShipmentNo" = Table.AddColumn(#"Filled Down to replace nulls in NewItem Index", "ShipmentNo", each ([Index.1]-[NewItem Index]+1), Int64.Type),

---

@edhans , thanks for the timely response! After following these steps and making a few changes (e.g. i can't filter out the null as this would mean taking out actual data), this is what i get for the index (FinalIndex column): 

this does not quite give me the same index i was looking for in the questions (above) - unless of course i'm doing it wrong - but this is a great trick too, thanks for your response!  

[Anonymous · Accepted solution]

---

@Michal_cwiok wrote:

I have also managed to do the job.

First step similar to what you have:

Then I add index that starts with 0.

 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1)

Next step is to compare a value to a value in a previous row i.e. with [Index]-1. I then add a column with 1, if it changes or 0 otherwise:

Custom_index = Table.AddColumn(#"Added Index", "custom column", each if [Index] = 0 then 1 else if #"Added Index"{[Index]-1}[Category] = #"Added Index"{[Index]}[Category] then 0 else 1),

It shows as:

Afterwards I calculate cumulative sum:

Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),

Which looks like this:

Final step is to connect it all:

Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),

After cleaning up I get:

Whole code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElPTFIRxxsuEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Custom_index = Table.AddColumn(#"Added Index", "custom column", each if [Index] = 0 then 1 else if #"Added Index"{[Index]-1}[Category] = #"Added Index"{[Index]}[Category] then 0 else 1),
    Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),
    
    Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),
    #"Removed Columns" = Table.RemoveColumns( Add_columns,{"Column2", "Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Product"}, {"Column4", "Index"}})
in
    #"Renamed Columns"

The file is here.

---

@Michal_cwiok, this is a great solution - thank you!

could you please explain the cumulative sum in a little more detail ? I am only now starting to dig into M so i'm not sure what is happening with the "=>". Alternatively, if you have a reference where i could probably read about this, it would be much appreciated! 

Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),

Also, is there any way to have the Add_columns stem to take the column names from Custom_index and Cumulative_new instead of having to rename them afterwards?

Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),

Thank you!

[Michal_cwiok]

I have also managed to do the job.

First step similar to what you have:

Then I add index that starts with 0.

 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1)

Next step is to compare a value to a value in a previous row i.e. with [Index]-1. I then add a column with 1, if it changes or 0 otherwise:

Custom_index = Table.AddColumn(#"Added Index", "custom column", each if [Index] = 0 then 1 else if #"Added Index"{[Index]-1}[Category] = #"Added Index"{[Index]}[Category] then 0 else 1),

It shows as:

Afterwards I calculate cumulative sum:

Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),

Which looks like this:

Final step is to connect it all:

Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),

After cleaning up I get:

Whole code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElPTFIRxxsuEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Custom_index = Table.AddColumn(#"Added Index", "custom column", each if [Index] = 0 then 1 else if #"Added Index"{[Index]-1}[Category] = #"Added Index"{[Index]}[Category] then 0 else 1),
    Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),
    
    Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),
    #"Removed Columns" = Table.RemoveColumns( Add_columns,{"Column2", "Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Product"}, {"Column4", "Index"}})
in
    #"Renamed Columns"

The file is here.

[Anonymous]

---

@Michal_cwiok, can you please explain the Cumulative sum step in a little more detail? I'm only starting to dig into M language and i'm not sure what the "=>" part does.. and what "index" is in this case.. 

Alternatively, if you provide any reference material where I can read about these, it would be much appreciated! 

Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),

 Also, for the final Add_columns step, is there anyway to take the column names directly from Custom_index and Cumulative_new instead of having to rename the columns after the fact? 

Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),

Other than that, this is a great solution - thank you! 

[Michal_cwiok]

@Anonymous, I gave it another shot and managed to solve it in two lines. The whole code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElPTFIRxxsuEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    
    New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) =>  if current = 0 then {1} else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}),
    Add_columns= Table.FromColumns(Table.ToColumns(#"Added Index")&{New_app}),
    
    #"Removed Columns1" = Table.RemoveColumns(Add_columns,{"Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Product"}, {"Column3", "Index"}})
in
    #"Renamed Columns"

The new line:

New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) =>  if current = 0 then {1} else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}),

And to explain the List.Accumulate. The documentation is here. Additionally, please read this.

How this function works is it iterates through each row of the Index column and performs actions specified after "=>". Current represents the value from Index column in the current row. State is the result of the function specified or seed value in the first step.

 In our case seed is a list {0} with a single value - zero. We have to declare names of our parameters - state and current. After "=>" we declare our function.

The function in pseudocode:

If I am in the first row, I want to have a list with one value and this value should be 1. This part:

if current = 0 then {1}

Else if the category in the previous row is the same as category in the current row:

else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then

I want to append the list I have with the last value from the list. This part:

state & {List.Last(state)}

I take state - which is a list and append it with the last value from it, as a list.

If the category is different I want to append it with +1 value. This part:

state & {List.Last(state)+1}

This is tricky, no doubt about that. Just keep in mind two things:

1. & unions lists. So {1,2,3} & {4,5} is {1,2,3,4,5}.

2. If you put value in a bracket {}, it becomes a list.

Let me know if it explains everything.

[Element115]

Out of curiosity.. how fast is Power Query executing this on your machine?  Because on mine, with a 250K row in import mode, it takes hours and hours and still isn't finished.  Is this to be expected or not?

[Anonymous]

@Michal_cwiok thanks, this is exactlly the explanation i was looking for! 

one last thing - is there any yway around renaming the tables as the last step? in this specific case it's only 2/3 columns but in my actual data, i have 100+ columns that i would like to avoid having to rename. 

thanks! 

[Michal_cwiok · Accepted solution]

Yes, sorry about that. Did not notice that question.

Take a look at this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElPTFIRxxsuEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Custom_index = Table.AddColumn(#"Added Index", "custom column", each if [Index] = 0 then 1 else if #"Added Index"{[Index]-1}[Category] = #"Added Index"{[Index]}[Category] then 0 else 1),
    Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),
    #"Converted to Table" = Table.FromList(Cumulative_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //I merge two columns into a table
    Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),

    //I create a new table with column name mapping
    Column_rename = Table.FromColumns({Table.ColumnNames(Add_columns)}&{Table.ColumnNames(Custom_index)&{"Index_new"}},{"Old","New"}),
    //Convert each row to a list
    Column_rename_torows = Table.ToRows(Column_rename),
    //Rename it using the list
    Renamed_cols = Table.RenameColumns(Add_columns, Column_rename_torows )
in
    Renamed_cols

You have to create a mapping table and then renames the columns.

Let me know if this is clear.

Thanks!

[Michal_cwiok]

@Anonymous, I gave it another shot and managed to solve it in two lines. The whole code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElPTFIRxxsuEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    
    New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) =>  if current = 0 then {1} else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}),
    Add_columns= Table.FromColumns(Table.ToColumns(#"Added Index")&{New_app}),
    
    #"Removed Columns1" = Table.RemoveColumns(Add_columns,{"Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Product"}, {"Column3", "Index"}})
in
    #"Renamed Columns"

The new line:

New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) =>  if current = 0 then {1} else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}),

And to explain the List.Accumulate. The documentation is here. Additionally, please read this.

How this function works is it iterates through each row of the Index column and performs actions specified after "=>". Current represents the value from Index column in the current row. State is the result of the function specified or seed value in the first step.

 In our case seed is a list {0} with a single value - zero. We have to declare names of our parameters - state and current. After "=>" we declare our function.

The function in pseudocode:

If I am in the first row, I want to have a list with one value and this value should be 1. This part:

if current = 0 then {1}

Else if the category in the previous row is the same as category in the current row:

else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then

I want to append the list I have with the last value from the list. This part:

state & {List.Last(state)}

I take state - which is a list and append it with the last value from it, as a list.

If the category is different I want to append it with +1 value. This part:

state & {List.Last(state)+1}

This is tricky, no doubt about that. Just keep in mind two things:

1. & unions lists. So {1,2,3} & {4,5} is {1,2,3,4,5}.

2. If you put value in a bracket {}, it becomes a list.

Let me know if it explains everything.

[Anonymous · Accepted solution]

---

@Michal_cwiok wrote:

I have also managed to do the job.

First step similar to what you have:

Then I add index that starts with 0.

 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1)

Next step is to compare a value to a value in a previous row i.e. with [Index]-1. I then add a column with 1, if it changes or 0 otherwise:

Custom_index = Table.AddColumn(#"Added Index", "custom column", each if [Index] = 0 then 1 else if #"Added Index"{[Index]-1}[Category] = #"Added Index"{[Index]}[Category] then 0 else 1),

It shows as:

Afterwards I calculate cumulative sum:

Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),

Which looks like this:

Final step is to connect it all:

Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),

After cleaning up I get:

Whole code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElPTFIRxxsuEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Custom_index = Table.AddColumn(#"Added Index", "custom column", each if [Index] = 0 then 1 else if #"Added Index"{[Index]-1}[Category] = #"Added Index"{[Index]}[Category] then 0 else 1),
    Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),
    
    Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),
    #"Removed Columns" = Table.RemoveColumns( Add_columns,{"Column2", "Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Product"}, {"Column4", "Index"}})
in
    #"Renamed Columns"

The file is here.

---

@Michal_cwiok, this is a great solution - thank you!

could you please explain the cumulative sum in a little more detail ? I am only now starting to dig into M so i'm not sure what is happening with the "=>". Alternatively, if you have a reference where i could probably read about this, it would be much appreciated! 

Cumulative_new  = List.Skip(List.Accumulate(Custom_index [custom column],{0}, (sum,index) => sum& {List.Last(sum) + index})),

Also, is there any way to have the Add_columns stem to take the column names from Custom_index and Cumulative_new instead of having to rename them afterwards?

Add_columns= Table.FromColumns(Table.ToColumns(Custom_index)&{Cumulative_new}),

Thank you!

[danielntamasi]

this was really useful for me! Thx!

[v-piga-msft]

Hi @Anonymous,

It seems that you have had the solution, only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[Element115]

By the way, I am running this M code on a 250K table in import mode and it takes hours and hours and at time of writing it is still not finished!  Is this normal behavior for Power Query or should it be able to finish faster than this?  Because obviously, over time, the table will keep getting larger and if it will take 24 hours on every daily refresh to display something to the users, then this is not a workable solution.

Microsoft, what is going on?

[edhans]

Yes. Here is what I have done.

1. Create an index that begins with 0 for the list.
2. Create a 2nd index that begis with 1.
3. Now, Join the table to itself (merge) connecting Index 0 to Index 1. Make it a "Full Outer" merge so all rows from both tables will be retained. This will make the merged table one row off from the source table, which will allow you to do your comparison.
4. Expand the [Product] field in the merged table.
5. Get rid of the null row in the 2nd [Product] field. It is null because your source table had a 0 in the index and nothing to merge with in the index that started with 1.
6. Sort the table by index0 to ensure it is in order.
7. Add a comparison column that is: 

   if [item_no]<>[item_no.1] then [Index.1] else null

8. If the product numbers are equal, then it will pull the new index1 field, else it will pull null. 
9. Now, in the comparson column (I've called it NewItem Index], do a Fill Down so the nulls will be replaced with the number above it.
10. Finally, add a column that does this:

    ([Index.1]-[NewItem Index]+1)

11. that will generate a column where the number will change with each new product item.
12. Delete all of the temporary index columns.

Here are the actual M code steps in my query. You cannot just drop these in since my columns and yours are probably different. I was doing the same with shipment numbers rather than product numbers. The step preceding the first step below was called #"Sorted Rows" and my deleting of the extra junk happened later in my query so that isn't shown either.

    #"Added Index0" = Table.AddIndexColumn(#"Sorted Rows", "Index.0", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index0", "Index.1", 1, 1),
    #"Merged with self for indexes" = Table.NestedJoin(#"Added Index1",{"Index.0"},#"Added Index1",{"Index.1"},"NewColumn",JoinKind.FullOuter),
    #"Expanded comparison ItemNo" = Table.ExpandTableColumn(#"Merged with self for indexes", "NewColumn", {"item_no"}, {"item_no.1"}),
    #"Filtered null item_no" = Table.SelectRows(#"Expanded comparison ItemNo", each ([item_no] <> null)),
    #"Sorted Index.0" = Table.Sort(#"Filtered null item_no",{{"Index.0", Order.Ascending}}),
    // If the item comparison doesn't match, then pull Index.1, else fill with null.
    #"Added NewItem Index" = Table.AddColumn(#"Sorted Index.0", "NewItem Index", each if [item_no]<>[item_no.1] then [Index.1] else null),
    #"Filled Down to replace nulls in NewItem Index" = Table.FillDown(#"Added NewItem Index",{"NewItem Index"}),
    #"Added ShipmentNo" = Table.AddColumn(#"Filled Down to replace nulls in NewItem Index", "ShipmentNo", each ([Index.1]-[NewItem Index]+1), Int64.Type),

Edited to add clarifying text to step 3.

[Anonymous · Accepted solution]

---

@edhans wrote:

Yes. Here is what I have done.

1. Create an index that begins with 0 for the list.
2. Create a 2nd index that begis with 1.
3. Now, Join the table to itself (merge) connecting Index 0 to Index 1. This will make the merged table one row off from the source table, which will allow you to do your comparison.
4. Expand the [Product] field in the merged table.
5. Get rid of the null row in the 2nd [Product] field. It is null because your source table had a 0 in the index and nothing to merge with in the index that started with 1.
6. Sort the table by index0 to ensure it is in order.
7. Add a comparison column that is: 

   if [item_no]<>[item_no.1] then [Index.1] else null

8. If the product numbers are equal, then it will pull the new index1 field, else it will pull null. 
9. Now, in the comparson column (I've called it NewItem Index], do a Fill Down so the nulls will be replaced with the number above it.
10. Finally, add a column that does this:

    ([Index.1]-[NewItem Index]+1)

11. that will generate a column where the number will change with each new product item.
12. Delete all of the temporary index columns.

Here are the actual M code steps in my query. You cannot just drop these in since my columns and yours are probably different. I was doing the same with shipment numbers rather than product numbers. The step preceding the first step below was called #"Sorted Rows" and my deleting of the extra junk happened later in my query so that isn't shown either.

    #"Added Index0" = Table.AddIndexColumn(#"Sorted Rows", "Index.0", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index0", "Index.1", 1, 1),
    #"Merged with self for indexes" = Table.NestedJoin(#"Added Index1",{"Index.0"},#"Added Index1",{"Index.1"},"NewColumn",JoinKind.FullOuter),
    #"Expanded comparison ItemNo" = Table.ExpandTableColumn(#"Merged with self for indexes", "NewColumn", {"item_no"}, {"item_no.1"}),
    #"Filtered null item_no" = Table.SelectRows(#"Expanded comparison ItemNo", each ([item_no] <> null)),
    #"Sorted Index.0" = Table.Sort(#"Filtered null item_no",{{"Index.0", Order.Ascending}}),
    // If the item comparison doesn't match, then pull Index.1, else fill with null.
    #"Added NewItem Index" = Table.AddColumn(#"Sorted Index.0", "NewItem Index", each if [item_no]<>[item_no.1] then [Index.1] else null),
    #"Filled Down to replace nulls in NewItem Index" = Table.FillDown(#"Added NewItem Index",{"NewItem Index"}),
    #"Added ShipmentNo" = Table.AddColumn(#"Filled Down to replace nulls in NewItem Index", "ShipmentNo", each ([Index.1]-[NewItem Index]+1), Int64.Type),

---

@edhans , thanks for the timely response! After following these steps and making a few changes (e.g. i can't filter out the null as this would mean taking out actual data), this is what i get for the index (FinalIndex column): 

this does not quite give me the same index i was looking for in the questions (above) - unless of course i'm doing it wrong - but this is a great trick too, thanks for your response!  

[edhans]

Filtering out the null won't cause you to lose any data. I didnt' specify this in the post but my code shows it - the merge is a FULL OUTER so it has data from both tables and there is an extra row from that when you expand the 2nd table. The entire top row will be null except perhaps data in the expanded table, but it will be bogus data as it is merging with "null" data.

Also, I think @Michal_cwiok's solution will be more relevant. What mine does is counts the number of times each item occurs then starts over with 1 with the next item. I could modify it of course, but Michal's solution does it in less steps.