I have a column with account number
Another column has charges for that account number. There could be 100 charges per account number.
Charges have description, quantity and amount as well as other things.
I would like to search the charges for a specific charge description and if it is there, add a column to table with account number with a value of 1, if it is not there mark it as a value of 0.
What I'm trying to do is get a total cost vs charge for people with this specific charge and a total cost vs charge for people without this specific charge.
Solved! Go to Solution.
This is easily done in Power Query. Add a Conditional Column that outputs 1 for any item in another column that contains the text you are looking for, and 0 otherwise.
Erik, I have tried the "if column = then 1 else 0 but it puts it in the table with the charges and then when I go to sum charges for those accounts, it only sums the column that has the 1.
Maybe I"m not following you.
Account Desc Amount
123456 Charge 1 100
123456 Charge 2 125
123456 Charge 3 250
654321 Charge 1 100
654321 Charge 3 250
654321 Charge 4 300
I can get it to put a new column, lets say I call it "special" next to amount if desc = Charge 2
But when I go to get total charges for all with "special" as 1, it is only totalling the charges with a description of charge 2
I need any charge with that account number to be marked as 1 so my totals are correct.
I hope I am making sense.
Yes I think I understand now. Try defining Special as follows:
Special = IF ( COUNTROWS ( FILTER ( Data, Data[Account] = EARLIER ( Data[Account] ) && Data[Desc] = "Charge 2" ) ) > 0, 1, 0 )
Do I put this in the table with the charges or with the single account number?
Table V_Acct has field ACCTNUM with unique account numbers for everyone
Table V_AcctCharges has the fields with charges.
Just to be clear, you could put the new column in either table. If you put it in the main account table you would have to modify the FILTER formula but it would still work and actually be somewhat more efficient. But it depends on how you are going to use it which I can't really tell.
Ok - this is what I did and it put a 0 in all the fields.
Off Formulary = IF(COUNTROWS(FILTER(V_ACCT_CHARGES,V_ACCT_CHARGES[AcctNum]=EARLIER(V_ACCT_CHARGES[AcctNum]) && V_ACCT_CHARGES[ChargeDescription]="KNEE MED I"))>0,1,0)
Where did I mess up?
How I want to use it is to who a line and cluster column chart that shows the total charges of a case that is considered "special" with the number of cases done
Then another one that shows the same thing with cases that are not considered "Special"