Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Hi @seekerMelbourne ,
did you check out this video already?:
(1) NestedIndex in PowerBI - YouTube
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
Thank you very much. It all makes sense now 🙂
Hi. I'm trying to do something different about the cases exposed.
I have a table that need an increase index when I have some change between three columns. I mean:
Worker | Fecha | Valor | Index |
A | 16/04/2018 | TRUE | 1 |
A | 16/04/2018 | TRUE | 1 |
A | 16/04/2018 | FALSE | 2 |
A | 16/04/2018 | TRUE | 3 |
B | 15/04/2018 | TRUE | 4 |
B | 16/04/2018 | FALSE | 5 |
B | 16/04/2018 | TRUE | 6 |
B | 16/04/2018 | TRUE | 6 |
Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution.
Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table.
In Excel is very easy because yo can calculate from the information in the last cell, but here I can't.
The excel formula could be:
=SI(A2=A1;SI(C2="FALSE";SI(C1="FALSE";E1;E1+1);SI(C1="FALSE";E1+1;E1));E1+1)
Yes, that's possible. No use for the grouping in this case, as your index shall continue to run. The only thing you need here is a reference to your previous row. For performance reasons, I recommend this method:
https://www.youtube.com/watch?v=xN2IRXQ2CvI
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. I'm trying to do something different about the cases exposed.
I have a table that need an increase index when I have some change between three columns. I mean:
Worker | Fecha | Valor | Index |
A | 16/04/2018 | TRUE | 1 |
A | 16/04/2018 | TRUE | 1 |
A | 16/04/2018 | FALSE | 2 |
A | 16/04/2018 | TRUE | 3 |
B | 15/04/2018 | TRUE | 4 |
B | 16/04/2018 | FALSE | 5 |
B | 16/04/2018 | TRUE | 6 |
B | 16/04/2018 | TRUE | 6 |
Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution.
Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table.
I have used this procedure succefully to obtian rankings based on groups, however, when mergeing with another table to gather detailed information, upon expanding the Table Column, the resulting values get scrambled. Only those generated using the Group and Index procedure... Could it be a Power Query Bug?
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
Hi, it looks like the exact solution that I am looking for but I not so well-versed in Power Query and unable to follow how to implement it in my work. Can someone show it in steps? For a start I have a table that I want to index based upon Week-ending date.
Thank you so much.
Is there any chance to implement your beautiful solution in direct query model?
Unfortunately this doesn't work in Direct Query mode and I can also not think of a workaround unfortunately.
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
I have used this procedure succefully to obtian rankings based on groups, however, when mergeing with another table to gather detailed information, upon expanding the Table Column, the resulting values get scrambled. Only those generated using the Group and Index procedure... Could it be a Power Query Bug?
It might help if you buffer the group-step (Table.Buffer).
Otherwise I would need more detailled information of how the scramble looks like/what exactly is the problem.
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
Thank you so much! It worked fine. After using the Table.Buffer it worked perectly...!
Imke
nice? Is this hand coded? If so, can you explain it? Is is like a partition over clause in TSQL?
Hi Matt,
good point – it’s handwritten. Actually in this case we wouldn’t have to nest it in but could also have added a new column with a handwritten Table.AddIndexColumn-command instead. Or a separate function. Might be a matter of taste at the end. (But I’d recommend to delete the other column before expanding).
let Source = Table1, Partition = Table.Group(Source, {"Group"}, {{"Partition", each _, type table}}), #"Added Custom" = Table.AddColumn(Partition, "Custom", each Table.AddIndexColumn([Partition], "Index", 1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Partition"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded Custom"
What many people don’t know about is the syntax sugar around “each” and that the last argument of Table.AddColumn takes in a function. So no need to write “() =>”, just take “each”.
I’ve given this technique the name partition, because it returns the same like the PARTITION OVER and I prefer catchy names (but of course this could also lead to confusion). But if applied to a SQL-source it will be executed as GROUP BY (if it comes with a statement that would fold, like SUM or AVERAGE – in our example here with an Index it needs to return all row, so no folding would take place on the server).
But still: This technique is a performance saver when it comes to iterative operations like running totals (don't Table.SelectColumns (equivalent to WHERE) - because they would always iterate over the whole table!!). But if you are querying SQL-sources a native PARTITION OVER SQL-query would be even faster.
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
I want to use this code that you have kindly provided in this thread to solve the same issue I am experiencing. However, I am new to power bi and i cannot get it to work. This is what i have done already and now i want to slot your code in to add this indexed column. The only difference is "Group" would be "Ticker" in my case. Can you help please?
let Source = Excel.Workbook(File.Contents("C:\Users\Exceldata.xlsx"), null, true), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Expanded Column2" = Table.ExpandTableColumn(#"Promoted Headers", "Column2", {"Column1", "Column10", "Column11", "Column12", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column2.Column1", "Column2.Column10", "Column2.Column11", "Column2.Column12", "Column2.Column2", "Column2.Column3", "Column2.Column4", "Column2.Column5", "Column2.Column6", "Column2.Column7", "Column2.Column8", "Column2.Column9"}), #"Promoted Headers1" = Table.PromoteHeaders(#"Expanded Column2"), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers1", each ([Date] <> "Date")), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"GDP CQOQ Index", "Ticker"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"GDP CQOQ Index_1", "Column16", "Sheet"}), #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","#N/A N/A"," ",Replacer.ReplaceValue,{"Ticker", "Date", "BN_SURVEY_HIGH", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ACTUAL_RELEASE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE", "PX_LAST"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#N/A Field Not Applicable"," ",Replacer.ReplaceValue,{"Ticker", "Date", "BN_SURVEY_HIGH", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ACTUAL_RELEASE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE", "PX_LAST"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value2",{{"Date", type date}, {"PX_LAST", type number}, {"ACTUAL_RELEASE", type number}, {"FIRST_REVISION", type number}, {"BN_SURVEY_LOW", type number}, {"BN_SURVEY_AVERAGE", type number}, {"BN_SURVEY_MEDIAN", type number}, {"BN_SURVEY_HIGH", type number}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Ticker", "Date", "PX_LAST", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_HIGH", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ECO_RELEASE_DT", "ACTUAL_RELEASE", "ECO_FUTURE_RELEASE_DATE"}), #"Added Custom" = Table.AddColumn(#"Reordered Columns", "BN_Surevy Range", each [BN_SURVEY_HIGH]-[BN_SURVEY_LOW]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"BN_Surevy Range", type number}}), #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Ticker", "Date", "PX_LAST", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_HIGH", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "BN_Surevy Range", "FIRST_REVISION_DATE", "FIRST_REVISION", "ECO_RELEASE_DT", "ACTUAL_RELEASE", "ECO_FUTURE_RELEASE_DATE"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"BN_Surevy Range", "BN_Survey Range"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Actual Vs Consensus", each [ACTUAL_RELEASE]-[BN_SURVEY_MEDIAN]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"ECO_RELEASE_DT", type text}}), #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each Date.FromText([ECO_RELEASE_DT])), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom", type date}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Eco Release Date"}}), #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"ECO_FUTURE_RELEASE_DATE", type text}}), #"Added Custom3" = Table.AddColumn(#"Changed Type4", "Eco Future Release Date", each Date.FromText([ECO_FUTURE_RELEASE_DATE])), #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"Eco Future Release Date", type date}}), #"Changed Type6" = Table.TransformColumnTypes(#"Changed Type5",{{"FIRST_REVISION_DATE", type text}}), #"Added Custom4" = Table.AddColumn(#"Changed Type6", "First Revision Date", each Date.FromText([FIRST_REVISION_DATE])), #"Changed Type7" = Table.TransformColumnTypes(#"Added Custom4",{{"First Revision Date", type date}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type7",{"FIRST_REVISION_DATE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE"}), #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each true) in #"Filtered Rows1"
You're welcome. Please check out this video:
and let me know if you need further help on this.
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
Awesome. Great video. The video definitely helped explain things. Thanks
Hi @ImkeF
I experimented your method to rank my data with two criterion. However, when I took a look at the data carefully, I found some data which is not ranked properly.
WEIGHT | HEIGHT | RANK | CORRECT RANK | |
A | 65 | 1.7 | 2 | 3 |
B | 65 | 1.8 | 3 | 2 |
C | 70 | 1.75 | 1 | 1 |
A,B,and C three people are ranked firstly by weight, if some of them have the same weight then we will rank them by height. I got the rank column which is not correct. Here I also show you the correct ranking.
So I tried to the buffer table query to conserve the sort, unfortunately, the issue has not gone so far.
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
This is your query. The difference is that I rank based on two values - weight and height. Also, I consider merge later, as I have data which has the same weight and height, so I want them to have the same ranking. I used buffer table after Partition to fix the issue, the issue is still there.
I suspect that in the Partition, they sort weight and height as any type of numbers, because later when I expand the table (in the LookupTable step), the column weight and height come with any data type.
How do you view this case?
I appreciate any help.
Thank you.
Best, Qianru
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |