How to create large groups / buckets in power query

[anwilkins]

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

[danextian]

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:

1.  Create a left outer join/merge in Power Query between this table of unpivoted columns and the fact table and not load the former or
2. Load the former and create a relationship between the fact table and the table of unpivoted columns and use it as a dimension table.

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"

1.  

Mer

[anwilkins]

Thank you so much for replying! 

[Anonymous · Accepted 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.

[anwilkins]

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,

Allowed Units =

IF(MERGE_BillingSummary[Current Insur Carrier] in {"AmeriHealth Caritas North Carolina", "AmeriHealth - Medicaid", "AmeriHealth - NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] >= 21, 8,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"AmeriHealth Caritas North Carolina", "AmeriHealth - Medicaid", "AmeriHealth - NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] <= 20, 16,

I'm not sure the same table strucure will work since I am know looking at 2 tables so do you have any thoughts on how to set the rules according to the adult allowables then use an eles statement? Something like...

If  insurance = AmeriHealth Medicaid  & Age >= 21 then 8, else 16

Is is the entire sample data...the items at the bottom I am waiting on the allowed units and the reauthorization column is used to set a conditional formatting flag.

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

 Thanks again for any further insight you have!

Ashley

[anwilkins]

This is PERFECT!!!!!! Thanks you so much!!!!