Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |