Adding conditional index based on changing field in Power Query M

---

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!  

---

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!

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!