cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stevenwan110 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
stevenwan110 Frequent Visitor
Frequent Visitor

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

 

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!  

stevenwan110 Frequent Visitor
Frequent Visitor

Re: Adding conditional index based on changing field in Power Query M


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

Michal_cwiok Regular Visitor
Regular Visitor

Re: Adding conditional index based on changing field in Power Query M

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!

11 REPLIES 11
edhans New Contributor
New Contributor

Re: Adding conditional index based on changing field in Power Query M

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.

Michal_cwiok Regular Visitor
Regular Visitor

Re: Adding conditional index based on changing field in Power Query M

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.

 

stevenwan110 Frequent Visitor
Frequent Visitor

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

 

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!  

stevenwan110 Frequent Visitor
Frequent Visitor

Re: Adding conditional index based on changing field in Power Query M


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

stevenwan110 Frequent Visitor
Frequent Visitor

Re: Adding conditional index based on changing field in Power Query M







@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 Regular Visitor
Regular Visitor

Re: Adding conditional index based on changing field in Power Query M

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

Highlighted
Michal_cwiok Regular Visitor
Regular Visitor

Re: Adding conditional index based on changing field in Power Query M

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

edhans New Contributor
New Contributor

Re: Adding conditional index based on changing field in Power Query M

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.

Community Support Team
Community Support Team

Re: Adding conditional index based on changing field in Power Query M

Hi @stevenwan110,

 

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.