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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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