Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm looking to add an index column, but have it increase according to a certain column value. Let me give an example; let's say my data is:
Group | Date |
A | 18-Apr |
A | 19-Apr |
A | 23-Apr |
A | 1-May |
B | 21-Apr |
B | 21-Apr |
B | 30-Apr |
B | 4-May |
And I would like to have the indices show like this:
Group | Date | Index |
A | 18-Apr | 1 |
A | 19-Apr | 2 |
A | 23-Apr | 3 |
A | 1-May | 4 |
B | 21-Apr | 1 |
B | 21-Apr | 2 |
B | 30-Apr | 3 |
B | 4-May | 4 |
How can I perform this dynamically?
Solved! Go to Solution.
Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:
let Source = Table1, Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded Partition"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF, I tried all the examples and nothing happened.For each week it is necessary to make a rating of values.I did not receive such a result
Many thanks @ImkeF this is fantastic. However I am trying to use this in DirectQuery mode and it is stumbling at the last step: 'Table.ExpandTableColumn' - I get an error message saying 'This step results in a query that is not supported in DirectQuery mode'.
Might you be able to help?
Kind regards,
Will
Hi ImkeF,
Thank you for your reply. But I didn't get it working...So I was trying the same sample data as below, and getting results as the image shows. Can you please help to check what could I missed? Thanks.
(Note: Column1 =Group column, Column2 = Date column)
let
Source = Excel.Workbook(File.Contents("C:\Users\dongl\Desktop\IndexTest.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Partition = Table.Group(Sheet1_Sheet, {"Column1", "Column2"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Column2", Order.Ascending}}), "Index",1,1), type table}}),Lookup = Table.NestedJoin(Sheet1_Sheet,{"Column1", "Column2"},Partition,{"Column1", "Column2"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(Lookup, "NewColumn", {"Partition"}, {"NewColumn.Partition"}),
#"Expanded NewColumn.Partition" = Table.ExpandTableColumn(#"Expanded NewColumn", "NewColumn.Partition", {"Index"}, {"NewColumn.Partition.Index"})
in
#"Expanded NewColumn.Partition"
Sorry, this couldn't work.
Here is the new code (tested 😉 😞
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00DMw0TMyMDRXitXBKWRkjCFkYKhnYIoQcgKpMkRRhUPI2ACvUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
RemoveDups = Table.Distinct(#"Changed Type", {"Group", "Date"}),
Partition = Table.Group(RemoveDups, {"Group"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),
LookupTable = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}),
Lookup = Table.NestedJoin(#"Changed Type",{"Group", "Date"},LookupTable,{"Group", "Date"},"NewColumn",JoinKind.LeftOuter),
ExpandedIndex = Table.ExpandTableColumn(Lookup, "NewColumn", {"Index"}, {"Index"})
in
ExpandedIndex
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi ImkeF,
Your solution works great! You are so smart! Thank you!
Excellent solution - and thanks for uploading the video.
Just one question, how can I choose which column to rank on?
Runar Wigestrand
Not sure if I understood you correctly, but I think you mean the field/column to group on? In this example it was also called Group:
Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You are a genious!!!
Hi, Imke.
Thanks for the quick reply. I was thinking more about if you have duplicates and want to keep only the newest record based on date. Then I want to group on one column and rank on another column ( for example the date column).
Something like this:
Group on SerialNum, rank on date column
Thanks.
Runar
Oh, I see: You need to fold in a Table.Sort-operation that will be executed before the Index is added. Sth like this:
Partition = Table.Group(Source, {"SerialNum"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date_Updated", Order.Ascending}}), "Index",1,1), type table}}),
Does that work for you?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi, Imke.
Perfect!
Thank you!
Runar Wigestrand
YES!!! Imke you are an absolute star 🙂
I have been stuck on this for weeks and weeks. The solution you have provided works perfectly. Thank you so much! What a great way to start the day...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |