Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Seeking an easy method to create a new column that groups 126 insurance vendors into 5 categories: Commercial, Grant, Medicaid, Medicare, Uninsured or N/A. (The N/A bucket will be the catch all for when new insurance vendors are added an we [the data team] are not notified. Once I see a person fall into that category I can update the list with the new company.)
In the past I created a new column using the following (very long) DAX statement (ex...
Ins Grps =
If(MERGE_BillingSUMMARY[Credited Insur Carrier] IN {"Aetna", "All Savers UHC", "BCBS - Med Adv. - OTP", "BCBS NC", "Beacon Health Options", "Zelis"}, "COMMERCIAL",
IF(MERGE_BillingSUMMARY[Credited Insur Carrier] IN {"Alliance - Medicaid","AmeriHealth - Medicaid", "COVID Medicaid", "Eastpoint - Medicaid", "Healthy Blue - Medicaid", "Healthy Blue - "WellCare-Medicaid"}, "MEDICAID",
And so on for the other 3 categories...
Is there a way in power query to create this column so I do not have to manually type all 126 insurance companies?
Here is the sample data:
MEDICAID | COMMERCIAL | MEDICARE | UNINSURED | N/A |
Alliance - Medicaid | Aetna | BCBS - Medicare Advantage | **No Insurance Carrier | CHC Staff COVID Test |
AmeriHealth - Medicaid | All Savers UHC | First Carolina Medicare | ABC Grant | Department of Veterans Affairs |
AmeriHealth - NCHC Medicaid | BCBS - Med Adv. - OTP | First Carolina Medicare - OTP | Adolescent Disaster Grant | Imagine 360 - GPA |
CAMF | BCBS NC | Gateway Health Medicare Assured | Adult Disaster Grant | Megellan PPO |
CAMF & IP | Beacon Health Options | Healthy Blue - Medicare | ASOUD-Trillium IPRS | Meritian Health |
Cardinal Innovations Healthcare | Behavioral Health Systems | Humana HMO Medicare | Beaufort County Department of Social Services | TriWest Healthcare Alliance |
Cardinal Innovations Medicaid | Benefit Management Services | Humana PPO Medicare | Bladen DPI Grant | UHC - Surest |
Carolina Complete - NCHC Medicaid | Boon Administrators | Medicare | Bladen School | |
Carolina Complete Health - Medicaid | Bright Health Plan | Medicare - OTP | Brunswick County | |
COVID Medicaid | Carolina Behavioral Health Alliance | Medicare - Railroad | Brunswick County School | |
Eastpoint - Medicaid | CIGNA | Medicare - Railroad OTP | Brunswick Court | |
Eastpointe - Medicaid | ComPsych | Medicare Supplemental | COC Scholarship | |
Healthy Blue - Medicaid | Corporate Benefits Services | New Hanover Department of Social Services | Columbus School | |
Healthy Blue - NCHC Medicaid | Elderhaus | United Healthcare Medicare | Eastpoint - IPRS | |
Healty Blue - Medicaid | Humana Commercia | United HealthCare OTP | Eastpointe - IPRS | |
Magellan Health Services | Insurance Benefit Systems Administrators | WellCare Medicare | Eshelman Grant | |
Medicaid | Medcost Preferred | Federal Probation | ||
Medicaid Family Plan Waiver | MultiPlan | Grant/ORH MAP | ||
NC Healthchoice | Planned Administrators Inc | Grant/United Way | ||
Partners Behavioral Health Management | Tricare East Region | HARRTS | ||
Partners Medicaid | UHC - Bind | Humana | ||
Sandhills Center | UMR | IFPS Grant | ||
Trillium Medicaid | United Health Care Student Resources | International Union of Operating Engineers | ||
UHC Community Plan - Medicaid | United Healthcare | New Hanover County Detention Center | ||
UHC Community Plan - NCHC Medicaid | United HealthCare Shared Services | New Hanover County Schools | ||
Vaya Health Medicaid | UnitedHealthOne/Golden Rule | NHRMC | ||
WellCare - Medicaid | VA Administration Center | NHRMC - Ryan White | ||
WellCare - NCHC Medicaid | Zelis | OTP-Self Pay 100% | ||
WellCare-Medicaid | PATH - SAMSHA | |||
Pender DPI | ||||
PIPBHC Grant | ||||
PPW Grant | ||||
QRT Scholarship | ||||
RCC Grant | ||||
Roger Bacon Academy | ||||
RRS Grant | ||||
Save a Vet Now | ||||
Self Pay - 100% | ||||
Sliding Fee - Primary Care | ||||
Sliding Fee BH - 20% Discount | ||||
Sliding Fee BH - 40% Discount | ||||
Trilliam IPRS | ||||
Trillium Health Resources Accounts Payable | ||||
Trillium IPRS | ||||
U'Nique Community Scholarship |
Solved! Go to Solution.
Hi,
I would add that sample data as a manual table using power query and the enter data button pictured below:
Ensure that the headers are Commercial, Grant, Medicaid, Medicare, Uninsured or N/A then unpivot all columns.
You can then merge this into your original table using a left outer join and the [Credited Insur Carrier] as a the join key. Expand the columns and you'll have each insurance carrier and their category.
Hi @anwilkins ,
In Power Query, I would unpivot all these columns so they are in a form that will make it easy to do a lookup and do either:
Please refer to the sample M script below for the PQ approach:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVZRb6M4EP4rVqV9WW1uc3unvWcgTZMHEg7SVtq9fXBhEqwzds6YVPn3NzYGDCG7UtVSbH/fzDffjPn+/SHgnFGRA1mQGAqWU1Y8fHoIQAuKf8MozPoVBSQoLlRoegJc+/hxJ8lW1I2y5yOqFAOFC9EmIpmmxyOJ9i/bFTlArR9+fEKuChTbAOW6nNBxTjJ6AVWT502EL9ZM1dpASs4E7fnN1jAiT8io8XkFZ6p0BUITeSQvoAEXahIcjxTPz1DuTGge75Ceyew3fNwfkvv0/XpQSA51bohXrKY1EvdBbSt6YgLIH1+XuP8pCWwcURCvO8KdyfCJaninV+JiGxSuUVCwohQNn8GP4QScU0GSZN9Dk3+a5fLLV7I10YVAcyk65P1ZMylqfN++uJKQN+AV1VBl++fV4qAYmqGpECXNLJNiGs3hkFoyqgqUhGPhhbxQC+3WHVYIJb0wqXCPiyC7YgKVjaCpKOq5ifc+O8bbHKVCwWUj9JWMy5rJnCFWBurCcjAoGOcrWsqjJZ2L78foVx0EHJkmMcZyAsvjobsYUd1RjJwWIMgq2faFQKeiihlWy9m790skqzNHN845TmJlgqJigtVaUS1VbZWeEmV5KSXH/+8gz3VRqNip7GQhCXrEg+69G6pG1O8s/9fJ3XPYXvXgetLbivZyj/BTyriStJghmeTziJY+S4bCjxKItk+7YB5zLnilb/AmYwzlSuprXvqYWXNGCU3VqYkn2kc2OE5VXbJzhzjbKw5TnVELpHI+qn337OCdbCjaDjv2Vz6OJG+qt6aeiDOhnjrokRegStoYhGfBNM4urxE8J/kiu44e8Ocyc85H0XBoYrBTgsgQtHUYCe6Dx9RNp673h3SHq6JrQDcYbvvhFTGiaTp1CRwD7Puv5RvCx8dc4lhIFBxBtTPUzHJAvVD4RMk3OwumR8maVoxfbcOQV8ou9hKLcfgy10NmXBvSz/t0Q+Ig6RB2USd9KZntBnNA2NvEzwgHUe7DOFVfad98CRpFmOvvtteGKdWOPltmoz9J4eTSwdoFaXrIbuA8edpxFTLhdGmr3Z3IqChKHP81iZDJKvAcp+3O7TrJxqr3N4WP71uF2OpluimM+1OosVtbF1gjIIGwtcA08RyORGyQ/RnrpJk4kUdhblCwVrB8JnZjywY5XKEWd7mdXybN2N8tGiMyjH2e9xmmrXfbDVmJvwvf5fO0bYf3+bzQKx3f/B5++34v4POT5OYuSBveJbRJ46gD6ZtkJMVL4JtvnKgDMGP1arxeIt0M2jTtb8CZSw27f5EBP5IEP11+Xy4/TI8vvHPtTxIcNuaeDOJsE3Tbh1UQhRmVyfZ2aZuEm2hsvGExeb2z8nd6mBvo/Xoa3cNM5QlDCe3HU5DjNVxdb/ek2Z3T5vuVUPMVSnby/Xa5U20x0m1Y56ww3l+DKUGiWEXV1bbRT7eGRtsvyw/mOzE3Xvv17j9/trvtbFqNhvpkFfveebdvbNTL4tUmRfrGb4OeflyOV5/t9+tfO/YfXkpDH07r+ON/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MEDICAID = _t, #" COMMERCIAL" = _t, MEDICARE = _t, UNINSURED = _t, #"N/A" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"MEDICAID", type text}, {" COMMERCIAL", type text}, {"MEDICARE", type text}, {"UNINSURED", type text}, {"N/A", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Category"}, {"Value", "Item"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Item] <> null and [Item] <> ""),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Category", Order.Ascending}})
in
#"Sorted Rows"
Mer
Proud to be a Super User!
Thank you so much for replying!
Hi,
I would add that sample data as a manual table using power query and the enter data button pictured below:
Ensure that the headers are Commercial, Grant, Medicaid, Medicare, Uninsured or N/A then unpivot all columns.
You can then merge this into your original table using a left outer join and the [Credited Insur Carrier] as a the join key. Expand the columns and you'll have each insurance carrier and their category.
Thanks again for the outstanding directions on how to create a table and link each insurance company to a category of medicare, medicaid, uninsured, etc. I'm curious if and how to structure a similar table. In this scenerio I have 21 medicaid insurance carriers who have different rules regarding the number of times a particular cpt code can be used. This differs for adults and children.
When I first built the report it had 17 IF statements that basically said,
Insurance Carrier | Allowed Usage of a CPT Code For Adults | Allowed Usage of a CPT Code For Kids | Reauthorization Alert |
AmeriHealth - Medicaid | 8 | 16 | 4 |
Cardinal Innovations Healthcare | 8 | 16 | 4 |
Cardinal Innovations Medicaid | 8 | 16 | 4 |
Healthy Blue - Medicaid | 8 | 12 | 4 |
ASOUD-Trillium IPRS | 12 | 24 | 4 |
Trillium IPRS | 12 | 24 | 4 |
Eastpointe - IPRS | 16 | 24 | 4 |
Eastpointe - Medicaid | 16 | 24 | 4 |
WellCare - Medicaid | 20 | 20 | 4 |
Carolina Complete Health - Medicaid | 24 | 24 | 4 |
Trillium Medicaid | 24 | 24 | 4 |
Alliance - Medicaid | 52 | 52 | 4 |
UHC Community Plan - Medicaid | 9999 | 9999 | |
UHC Community Plan - NCHC Medicaid | 9999 | 9999 | |
AmeriHealth - NCHC Medicaid | |||
Carolina Complete - NCHC Medicaid | |||
Healthy Blue - NCHC Medicaid | |||
NC Healthchoice | |||
Vaya Health Medicaid | |||
WellCare - NCHC Medicaid |
This is PERFECT!!!!!! Thanks you so much!!!!
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |