cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saanah2019 Regular Visitor
Regular Visitor

Making a new column based on multiple column values

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 IdFirst Bill InsurancePayment InsuranceIntial ChargePayment amountBalance Leftfirst billdate transaction
100Aetnaaetna10001001/1/201810/1/2018
100Aetnafidelis10080201/1/201810/5/2018
200Fidelisfidelis200150501/1/201810/1/2018
300Healthfirsthealthfirst30003001/1/201810/1/2018
300Healthfirstaetna3002001001/1/201810/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 IdFirst Bill InsurancePayment InsuranceIntial ChargePayment amountBalance Leftfirst billdate transactionstatus
100Aetnaaetna10001001/1/201810/1/2018uncollectable
100Aetnafidelis10080201/1/201810/5/2018collectable
200Fidelisfidelis200150501/1/201810/1/2018colletable
300Healthfirsthealthfirst30003001/1/201810/1/2018uncollectable
300Healthfirstaetna3002001001/1/201810/6/2018collectable
18 REPLIES 18
saanah2019 Regular Visitor
Regular Visitor

Compare values from many columns

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 idinsurance billedamountpaymentbalance remaininginsurance paid 
2aetna1000100aetna
*2aetna1006040fidelis
*3aetna1001000aetna
3healthfirst1000100healthfirst
*4healthfirst1003070aetna
5fidelis1000100fidelis
*7fidelis1005050aetna
sum 700240460 

 

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.

Super User
Super User

Re: Compare values from many columns

@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 *





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




saanah2019 Regular Visitor
Regular Visitor

Re: Compare values from many columns

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. 

Super User
Super User

Re: Compare values from many columns

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




saanah2019 Regular Visitor
Regular Visitor

Re: Compare values from many columns

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

show all instances where first insurance does not equal payment insurance

saanah2019 Regular Visitor
Regular Visitor

Re: Compare values from many columns

so far I have tried 

 

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

Re: Making a new column based on multiple column values

@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 Regular Visitor
Regular Visitor

Re: Making a new column based on multiple column values

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 Established Member
Established Member

Re: Making a new column based on multiple column values

You can apply everything from

#"Added Index"