Conditional Index Column in Power Query

[MenalM]

Hi, 

I am struggling with an issue and need your help. I cannot find any solution.

I need to create an index column with specific requirements. My dataset includes several columns, among them Client, Contract, User, and Equipment. For each client, I may have multiple contracts; for each contract, there may be multiple users; and for each user, there may be multiple equipments. What I need is to have an index column that counts how many contracts I have for each client.

Specifically, the index should start at 1 for each new client, remain the same if the contract doesn't change, and increment if there is a new contract within the same client.

Could you please help me with this?

[MenalM]

Hi @Greg_Deckler , 

Thanks for your answer, but that's not what I need.

What I need is to reset the index for each new client, keep the same index within the same client if the contract doesn't change, and increment it if the contract changes.

It should work something like this:

Client	ContIndex	CE MFG	Utilisateur	Serie
1108	1	10444616	P0010782	YB50367U536837V
1108	1	10444616	S0017325	CD4039D422219
42100	1	10266618	P0005906	11836559
42100	1	10266618	P0005909	1025
67000003	1	140819	P0010354	1EZ17046
67000003	2	140820	P0010354	536103381
SK000008	1	10228719	P0010262	535101200
SK000008	1	10228719	P0010262	5,36102E+13
SK000008	1	10228719	P0010262	63785.
SK000008	1	10228719	P0010262	82433
SK000008	2	123577	S0020820	5306202480
SK000008	2	123577	S0020820	CD4045B189216

[slorin · Accepted solution]

Hi, @MenalM 

let
Source = Your_Source, 
Group = Table.Group(
   Source, 
   {"Client"}, 
   {{"Data1", 
     each Table.AddIndexColumn(Table.Group(_, {"Contract"}, {{"Data2", each _}}), "Index", 1)
   }}), 
Expand1 = Table.ExpandTableColumn(Group, "Data1", {"Data2", "Index"}, {"Data2", "Index"}), 
Expand2 = Table.ExpandTableColumn(Expand1, "Data2", {"Contract"}, {"Contract"})
in
Expand2

 Stéphane

[MenalM]

Thanks for your answer. 

This is an exemple of my input data : 

Client	Contract
Cl 1	Cr 1
Cl 1	Cr 1
Cl 2	Cr 2
Cl 3	Cr 3
Cl 3	Cr 4
Cl 4	Cr 5
Cl 5	Cr 6

And my wanted output is : 

Client	Index	Contract
Cl 1	1	Cr 1
Cl 1	1	Cr 1
Cl 2	1	Cr 2
Cl 3	1	Cr 3
Cl 3	2	Cr 4
Cl 4	1	Cr 5
Cl 5	1	Cr 6

I hpe this is now more clear for you.

[m_dekorte]

Hi @MenalM 

Follow these steps, make sure the Client column is selected

Choose Group By from the ribbon, set the operation to All Rows and and press OK

Go to the Add Column tab and Add an Index Column from 1

Final step is to expand missing fields, in this case Contract from the nested table.

Done.

I hope this is helpful

[Greg_Deckler]

@MenalM Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.