Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
@edhans wrote:Yes. Here is what I have done.
- Create an index that begins with 0 for the list.
- Create a 2nd index that begis with 1.
- 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.
- Expand the [Product] field in the merged table.
- 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.
- Sort the table by index0 to ensure it is in order.
- Add a comparison column that is:
if [item_no]<>[item_no.1] then [Index.1] else null- If the product numbers are equal, then it will pull the new index1 field, else it will pull null.
- 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.
- Finally, add a column that does this:
([Index.1]-[NewItem Index]+1)- that will generate a column where the number will change with each new product item.
- 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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |