Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Adding conditional index based on changing field in Power Query M

 

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! 

 

ProductDateIndex
I2018-05-121
I2018-05-131
I2018-05-141
A2018-05-142
A2018-05-142
I2018-05-153
I2018-05-153
I2018-05-163
A2018-05-174
I2018-05-195
A2018-05-206
A2018-05-206
I2018-05-217
A2018-05-218
I2018-05-219
I2018-05-219
A2018-05-2210
I2018-05-2211
I2018-05-2211
A2018-05-2412
A2018-05-2512
I2018-05-2713
A2018-05-2814
I2018-05-2815
A2018-05-2916
A2018-05-2916
A2018-05-3016
I2018-05-3017
A2018-06-0218
I2018-06-0319
A2018-06-0420
I2018-06-0421
I2018-06-0621
A2018-06-0622
I2018-06-0723
A2018-06-0724
A2018-06-0724
A2018-06-0824
A2018-06-0824
I2018-06-0825
3 ACCEPTED SOLUTIONS
Anonymous
Not applicable


@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): 

 

Index1.png

 

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!  

View solution in original post

Anonymous
Not applicable


@Michal_cwiok wrote:

I have also managed to do the job.

 

First step similar to what you have:

First_step.png

 

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:

second.png

 

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:

three.png

 

Final step is to connect it all:

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

After cleaning up I get:

final.png

 

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!

View solution in original post

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!

View solution in original post

14 REPLIES 14
Michal_cwiok
Resolver II
Resolver II

I have also managed to do the job.

 

First step similar to what you have:

First_step.png

 

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:

second.png

 

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:

three.png

 

Final step is to connect it all:

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

After cleaning up I get:

final.png

 

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
Not applicable







@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! 

@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.

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
Not applicable

@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! 

 



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!

@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
Not applicable


@Michal_cwiok wrote:

I have also managed to do the job.

 

First step similar to what you have:

First_step.png

 

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:

second.png

 

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:

three.png

 

Final step is to connect it all:

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

After cleaning up I get:

final.png

 

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!

this was really useful for me! Thx!

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable


@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): 

 

Index1.png

 

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!  

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.