Making a new column based on multiple column values

[saanah2019]

Hi, I am almost at the end of my report and just need some help with the last thing. so here is my sample data

Visit Id	First Bill Insurance	Payment Insurance	Intial Charge	Payment amount	Balance Left	first bill	date transaction
100	Aetna	aetna	100	0	100	1/1/2018	10/1/2018
100	Aetna	fidelis	100	80	20	1/1/2018	10/5/2018
200	Fidelis	fidelis	200	150	50	1/1/2018	10/1/2018
300	Healthfirst	healthfirst	300	0	300	1/1/2018	10/1/2018
300	Healthfirst	aetna	300	200	100	1/1/2018	10/6/2018

I want to create a new column with the following logic:

so if visit id appears more than twice and the first bill insurance does not equal the payment insurance, then look at the date of transaction and for the date that is greater, call that insurance payment name collectable and for the date that is lower, call that insurance name uncollectable ( or even just mark them 1 or 2 is fine).

I would like the result set to look like this preferablly. 

Visit Id	First Bill Insurance	Payment Insurance	Intial Charge	Payment amount	Balance Left	first bill	date transaction	status
100	Aetna	aetna	100	0	100	1/1/2018	10/1/2018	uncollectable
100	Aetna	fidelis	100	80	20	1/1/2018	10/5/2018	collectable
200	Fidelis	fidelis	200	150	50	1/1/2018	10/1/2018	colletable
300	Healthfirst	healthfirst	300	0	300	1/1/2018	10/1/2018	uncollectable
300	Healthfirst	aetna	300	200	100	1/1/2018	10/6/2018	collectable

[saanah2019]

Hi, so I am building a report for  patients based on 4 factors. their visit id, the initial insurance billed, the amount payed and the insurance that paid. so for example:

visit id	insurance billed	amount	payment	balance remaining	insurance paid
2	aetna	100	0	100	aetna
*2	aetna	100	60	40	fidelis
*3	aetna	100	100	0	aetna
3	healthfirst	100	0	100	healthfirst
*4	healthfirst	100	30	70	aetna
5	fidelis	100	0	100	fidelis
*7	fidelis	100	50	50	aetna
sum		700	240	460

so i want to create a report where only the four values with the * would be shown because the other charges will only increase my remaining balance and thus mess my data and make it wrong.  So in theory visit id is a unique field but it appears twice because of the fact that this data is from 2 different tables, one for payment and the other for the the visit id and the first insurance billed. Any help on starting this report would be appreciated. Thank you.

[parry2k]

@saanah2019 you can do it in two way, you can filter records in Power Query by clicking query editor or put page level/report level filter to exclude records without *

[saanah2019]

Hey thanks for replying. Just to be clear, there are no stars in the real data, I was just  pointing out which ones I wanted to see. 

[parry2k]

@saanah2019 ok got it, what would be the critiera to remove rows with *

[saanah2019]

Hey, so the logic would be something along these lines:

show all instances where first insurance does not equal payment insurance

[v-piga-msft]

Hi @saanah2019 ,

Have you solved your problem? If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, it seems that you have more than one table, could you show your table sample and  so that we could help further on it.

For your scenario, we could create a tag to calculate the rows which first insurance does not equal payment insurance, then we could drag the tag to visual level filter to only the the rows which first insurance does not equal paymen insurance.

Best  Regards,

Cherry

[saanah2019]

so far I have tried 

CheckInsurance = CALCULATE(COUNTROWS('Payment'), FILTER('Payment', 'Payment'[Insurance Name] = Earlier('Visit'[First Bill Insurance Name]))) > 0

[smpa01]

@saanah2019 

I achieved the result in this way

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Making-a-new-column-based-on-multiple-column-values/m-p/662129#M318166")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "OI", 1, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Visit Id", Int64.Type}, {"First Bill Insurance", type text}, {"Payment Insurance", type text}, {"Intial Charge", Int64.Type}, {"Payment amount", Int64.Type}, {"Balance Left", Int64.Type}, {"first bill", type date}, {"date transaction", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Visit Id"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let 
   Source = [AD],
   X= Table.AddIndexColumn(Table.Sort([AD],{{"date transaction", Order.Descending}}),"custom",1,1),
   Y = Table.AddColumn(X, "status", each if [custom]=1 then "collectable" else "uncollectable"),
   Z = Table.RemoveColumns(Y,{"custom"})
in
  Z),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Visit Id", "First Bill Insurance", "Payment Insurance", "Intial Charge", "Payment amount", "Balance Left", "first bill", "date transaction", "OI", "status"}, {"Visit Id", "First Bill Insurance", "Payment Insurance", "Intial Charge", "Payment amount", "Balance Left", "first bill", "date transaction", "OI", "status"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"OI", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"OI"})
in
    #"Removed Columns"

[saanah2019]

Hey thanks for your effort. I am just a little confused as to for me, which part of the code do I use only. 

[smpa01]

You can apply everything from

#"Added Index"

[saanah2019]

thanks alot, I am trying it now and if it gives me what i need i will mark your response as the solution and if not, I will let you know what happened. 

[saanah2019]

Hey, does it change anything if some of the data is in a different table? 

[saanah2019]

i have 2 tables: visit and payment
these are the fields from visit:
visit id:
first bill insurance:
initial charge
balance left

and from payment: payment insurance, payment amount, date transaction

@smpa01 

[smpa01]

@saanah2019my solution is based on the sample data that you shared. Before applying all the steps from #"Added Index" onwards please make sure that you are running the code on a single table that looks exactly loike the sample data.

Alternatively, you can share the new dataset with the correct table names as they appear on the query editor. I will try to get back to you.

[saanah2019]

@smpa01  thanks alot for your response. Sorry for not clarifying intially. 

visit table
Visit Id	First Bill Insurance	Intial Charge	Balance Left( calculated by subtracting intial charge and payment amount)	first bill
100	Aetna	100	100			1/1/2018
100	Aetna	100	100			1/1/2018
200	Fidelis	200	200			1/1/2018
300	Healthfirst	300	300			1/1/2018
300	Healthfirst	300	300			1/1/2018

payment table
Visit Id		Payment Insurance	Payment amount	date transaction
100		aetna		0	10/1/2018
100		fidelis		80	10/5/2018
200		fidelis		150	10/1/2018
300		healthfirst		0	10/1/2018
300		aetna		200	10/6/2018

end result
Visit Id	First Bill Insurance	Payment Insurance	Intial Charge	Payment amount	Balance Left	first bill	date transaction	status
100	Aetna	aetna	100	0	100	1/1/2018	10/1/2018	uncollectable
100	Aetna	fidelis	100	80	20	1/1/2018	10/5/2018	collectable
200	Fidelis	fidelis	200	150	50	1/1/2018	10/1/2018	colletable
300	Healthfirst	healthfirst	300	0	300	1/1/2018	10/1/2018	uncollectable
300	Healthfirst	aetna	300	200	100	1/1/2018	10/6/2018	collectable

[smpa01]

Hello @saanah2019 ,

My solution is based on the table that you provided initially

Visit Id	First Bill Insurance	Payment Insurance	Intial Charge	Payment amount	Balance Left	first bill	date transaction
100	Aetna	aetna	100	0	100	1/1/2018	10/1/2018
100	Aetna	fidelis	100	80	20	1/1/2018	10/5/2018
200	Fidelis	fidelis	200	150	50	1/1/2018	10/1/2018
300	Healthfirst	healthfirst	300	0	300	1/1/2018	10/1/2018
300	Healthfirst	aetna	300	200	100	1/1/2018	10/6/2018

However, you provided two separate tables called visit and payment. I like to believe that you must have written a code to achieve the above table from two separate tables (visit and payment). Once you reach that, please make sure that each header of that table matches to the headers of the table above and apply the code from apply index onwards by replacing #"Promoted Headers" with the name of the immediately preeceding step.

#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "OI", 1, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Visit Id", Int64.Type}, {"First Bill Insurance", type text}, {"Payment Insurance", type text}, {"Intial Charge", Int64.Type}, {"Payment amount", Int64.Type}, {"Balance Left", Int64.Type}, {"first bill", type date}, {"date transaction", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Visit Id"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let 
   Source = [AD],
   X= Table.AddIndexColumn(Table.Sort([AD],{{"date transaction", Order.Descending}}),"custom",1,1),
   Y = Table.AddColumn(X, "status", each if [custom]=1 then "collectable" else "uncollectable"),
   Z = Table.RemoveColumns(Y,{"custom"})
in
  Z),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Visit Id", "First Bill Insurance", "Payment Insurance", "Intial Charge", "Payment amount", "Balance Left", "first bill", "date transaction", "OI", "status"}, {"Visit Id", "First Bill Insurance", "Payment Insurance", "Intial Charge", "Payment amount", "Balance Left", "first bill", "date transaction", "OI", "status"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"OI", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"OI"})
in
    #"Removed Columns"

[saanah2019]

Hey @smpa01 , thanks for all your efforts. I am trying to use your solution by merging the two tables as one and then applying your solution. The only problem is that none of the joins show the correct numbers. the closest i got to achieving the result was the full outer join but the major issue was that it double all the values in one table but showed all the correct values for the other table. Do you know if I can fix this because then I will be able to use your solution?

[smpa01]

@saanah2019 Try adding an index column on both tables from 1 with increment of 1 and then try a left outer join. It might work.