How to query multiple columns for data without filters

[anwilkins]

Scenerio:

Each physician in the U.S. is required to be credentialed (a.k.a. - Approved) by an insurance carrier prior to submitting charges for service. Our Credentialling Department wants a report with two visuals:

1. A Bar Chart that shows the number of staff associated to each carrier (ex...
   1. NCTRACKS = 203
   2. AETNA = 9
   3. Trillium = 178
2. A list of staff with the name of each company they are credentialed by 

The obsticle:

The source data is from a SharePoint List that contains 20 columns representing the current insurance carriers. This field was created as a single select radio button 

   

The result is a table structure of choices (full sample data is supplied below)

For the Bar Chart, I need to know how to extract a count without using the filter because they override each other.

My company security policy will not allow me to attach data however, below I copied an pasted in a scrubbed version that I hope is usable.

First Name of Provider

NCTRACKS

AETNA

ALLIANCE

Ambetter

BCBS

CIGNA

EastPointe

EverNorth

HUMANA

MAGELLEN

MEDCOST

MEDICARE

OPTUM Commercial Behavioral Health

PARTNERS

Railroad Medicare

TRICARE(PREV MHN)

Trillium

UHC Commercial Medical

Vaya.

Wellcare

Janet

Approved

Approved

N/A

Approved

Jacob

Approved

Approved

Approved

Approved

Approved

Approved

Mary

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Andrew

Approved

Approved

N/A

Approved

Kacie

Approved

N/A

N/A

N/A

Kristen

Approved

N/A

Approved

Approved

Approved

Approved

Approved

Sydneigh

Approved

Approved

N/A

N/A

N/A

Submitted

Approved

Approved

Charles

Approved

Approved

Approved

Approved

Approved

N/A

Joseph

Approved

Approved

Approved

Submitted

Approved

Jazz

Approved

N/A

N/A

Submitted

Approved

Rachel

Approved

Submitted

N/A

Approved

Jeffrey

Approved

Approved

N/A

Approved

Gayle

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Sarah

Approved

Approved

Approved

Approved

N/A

Approved

Approved

Hannah

Submitted

Needed or Possibly Needed

N/A

Submitted

Needed or Possibly Needed

N/A

N/A

Needed or Possibly Needed

Heather

Approved

Submitted

Approved

Approved

N/A

Approved

Approved

N/A

Submitted

Jason

Approved

Approved

Denied

Approved

Approved

Approved

Approved

Denied

Approved

Ana

Approved

Approved

Approved

Approved

Denied

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Angela

Approved

Submitted

Approved

Submitted

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Karimah

Approved

Approved

Submitted

Approved

Susan

Approved

Submitted

Approved

Approved

N/A

Submitted

Approved

Approved

Approved

N/A

Lauren

Approved

Approved

Approved

Submitted

Approved

Sarah

Approved

Approved

Submitted

Submitted

Approved

Approved

Approved

N/A

Aimee

Approved

Approved

Approved

Approved

Approved

Richard

Approved

Approved

Approved

Approved

Submitted

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Rebeca

Approved

Approved

N/A

Approved

Tashema

Approved

N/A

N/A

N/A

Submitted

Beth

Approved

Approved

Approved

Submitted

Approved

Submitted

Approved

Approved

Approved

Approved

N/A

Approved

Brittany

Approved

N/A

N/A

N/A

Submitted

Brandon

Submitted

Needed or Possibly Needed

N/A

Needed or Possibly Needed

Needed or Possibly Needed

N/A

N/A

Needed or Possibly Needed

Needed or Possibly Needed

Josette

Approved

Submitted

Approved

Kristina

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Kelly

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Approved

Kaitlyn

Approved

N/A

N/A

N/A

Amanda

Approved

Submitted

Approved

Approved

Approved

Submitted

Approved

Approved

Meredith

Approved

Approved

Approved

Approved

N/A

Approved

Approved

Approved

Rachel

Approved

Approved

Denied

Approved

Approved

Approved

Approved

Approved

Amanda

Approved

Approved

Approved

Submitted

Approved

Approved

Approved

Alyson

Approved

Approved

Approved

N/A

Approved

Christopher

Approved

Approved

Approved

Approved

Approved

Submitted

Approved

Approved

Approved

Submitted

Approved

N/A

Ayrien

Approved

Approved

Approved

Approved

Approved

Ross

Approved

N/A

Approved

Sean

Approved

N/A

Approved

N/A

Submitted

Approved

Glenda

Approved

Approved

N/A

Approved

Approved

Ashley

Approved

Approved

Approved

Approved

Breana

Approved

Approved

Approved

N/A

Approved

Approved

Shante

Approved

Approved

N/A

Approved

Approved

Elizabeth

Approved

N/A

Approved

Lani

Approved

Approved

Submitted

N/A

Approved

Approved

Approved

N/A

Approved

Approved

Approved

Thanks for any ideas you can share

Ashley

[anwilkins]

For other beginners I will add a bit of detail on the process as the Applied Steps were pretty simplistic given the modificatins I'd made to my sample data. So if starting from scratch:

1. I opened query editor and duplicated my original table
2. I went to Choose Columns and of the 78, kept only the 22 that I needed. (20 contained the name of the vendors with their status, 1 was an id field and the last was the full name of the employee)
3. I then highlighted the ID and Name fields, the 'unpivoted other columns'
4. This left me with the desired 4 columns as shown in the sample pbx. 

Thanks again Ashish!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[anwilkins · Accepted solution]

Ashish, This is the EXACT result that I was hoping for! Thanks you so very much! Now....Since my data is being modified weekly, can you tell me how you created this table, and how it will  link to my existing data that I pull from SharePoint each week? If I am correct in my assumption, either I will need to add these 3 columns to my existing table or I will need to build a second table (both of which will require a bit more direction from you if possible). Thanks

[Ashish_Mathur]

You are welcome.  Please go through the steps in the Applied Steps pane of the Query Editor to understand the transformation i have carried out. 

[anwilkins]

For chart one my hope is to create a bar chart of all the insurance carriers and the number of our clinicians who are "approved" by them. It should look like this:

For item 2, I need a way to display each of the 203 employees with a list of each carrier....sort of like above but this time a visual display of who has what...I have no idea how to best display that or how to get the info in 1 chart as its is in 20 different columns.

Hope that helps.

[dcrosseto]

Oh, that's tricky.

With this table, I think you will not be able to do this calculation, you'll have to create a new one, maybe using PowerQuery M or SQL.

The "sub-query" of this table will have to look something like this to create this graph:

Category	Count_Approved
AETNA	123
BCBS	98
Trillium	200

Hope it helped.

Regards,

Douglas.

[anwilkins]

Thanks for taking a look. 

[dcrosseto]

Hi @anwilkins , 

I couldn't understand your question, could you please provide a drawing or something like that of what you're expecting to see? Like a Actual vs. Expected visual.

It would help me and the others to help you 😊

Regards,

Douglas.