Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
@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 *
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@saanah2019 ok got it, what would be the critiera to remove rows with *
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hey, so the logic would be something along these lines:
show all instances where first insurance does not equal payment insurance
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
so far I have tried
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"
Hey thanks for your effort. I am just a little confused as to for me, which part of the code do I use only.
You can apply everything from
#"Added Index"
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.
Hey, does it change anything if some of the data is in a different table?
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
@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.
@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 |
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"
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?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |