Forum Discussion
Conditional Index Column in Power Query
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?
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
Expand2Stéphane
5 Replies
slorinSuper User
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
Expand2Stéphane
- Greg_Deckler
Community Champion
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. 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
Resident Rockstar
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
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
