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:A Bar Chart that shows the number of staff associated to each carrier (ex...NCTRACKS = 203AETNA = 9Trillium = 178A 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 ProviderNCTRACKSAETNAALLIANCEAmbetterBCBSCIGNAEastPointeEverNorthHUMANAMAGELLENMEDCOSTMEDICAREOPTUM Commercial Behavioral HealthPARTNERSRailroad MedicareTRICARE(PREV MHN)TrilliumUHC Commercial MedicalVaya.WellcareJanetApproved Approved N/A Approved JacobApproved Approved Approved ApprovedApproved Approved MaryApproved Approved Approved Approved ApprovedApproved Approved Approved AndrewApproved Approved N/A Approved KacieApproved N/A N/A N/A KristenApproved N/A Approved ApprovedApproved Approved Approved SydneighApproved ApprovedN/A N/A N/ASubmitted Approved Approved CharlesApproved Approved Approved Approved ApprovedN/A JosephApproved Approved Approved Submitted Approved JazzApproved N/A N/A Submitted Approved RachelApproved Submitted N/A Approved JeffreyApproved Approved N/A Approved GayleApproved ApprovedApproved Approved Approved Approved ApprovedSarahApproved Approved Approved Approved N/A Approved ApprovedHannahSubmitted Needed or Possibly NeededN/ASubmitted Needed or Possibly NeededN/A N/A Needed or Possibly Needed HeatherApprovedSubmitted ApprovedApprovedN/A ApprovedApproved N/ASubmitted JasonApproved ApprovedDeniedApproved ApprovedApproved Approved DeniedApproved AnaApproved ApprovedApprovedApprovedDeniedApproved ApprovedApprovedApprovedApprovedApprovedApprovedApprovedApprovedApprovedApproved AngelaApprovedSubmitted ApprovedSubmittedApproved ApprovedApprovedApprovedApproved Approved Approved KarimahApproved Approved Submitted Approved SusanApprovedSubmitted ApprovedApprovedN/A SubmittedApprovedApproved Approved N/A LaurenApproved Approved Approved Submitted Approved SarahApproved Approved Submitted Submitted ApprovedApproved ApprovedN/A AimeeApproved Approved Approved Approved Approved RichardApprovedApproved Approved Approved SubmittedApprovedApprovedApprovedApprovedApprovedApprovedApprovedApproved ApprovedApprovedRebecaApproved Approved N/A Approved TashemaApproved N/A N/A N/A Submitted BethApproved Approved ApprovedSubmittedApproved Submitted ApprovedApprovedApproved ApprovedN/AApproved BrittanyApproved N/A N/A N/A Submitted BrandonSubmitted Needed or Possibly NeededN/ANeeded or Possibly Needed Needed or Possibly NeededN/A N/ANeeded or Possibly Needed Needed or Possibly Needed JosetteApproved Submitted Approved KristinaApproved Approved Approved Approved ApprovedApproved Approved Approved KellyApprovedApproved ApprovedApprovedApproved ApprovedApproved Approved Approved Approved KaitlynApproved N/A N/A N/A AmandaApprovedSubmitted ApprovedApprovedApproved Submitted Approved Approved MeredithApproved Approved Approved Approved N/AApproved Approved Approved RachelApproved ApprovedDeniedApproved ApprovedApproved Approved Approved AmandaApproved Approved Approved Submitted ApprovedApproved Approved AlysonApproved Approved Approved N/A Approved ChristopherApprovedApproved ApprovedApprovedApproved Submitted ApprovedApproved ApprovedSubmittedApprovedN/A AyrienApproved Approved Approved Approved Approved RossApproved N/A Approved SeanApproved N/AApproved N/A Submitted Approved GlendaApproved Approved N/A Approved Approved AshleyApproved Approved Approved Approved BreanaApproved Approved Approved N/A Approved Approved ShanteApproved Approved N/A Approved Approved ElizabethApproved N/A Approved LaniApproved ApprovedSubmittedN/A Approved ApprovedApproved N/AApprovedApprovedApproved Thanks for any ideas you can shareAshley

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

Anonymous

Not applicable

3 years ago

Solved

How to query multiple columns for data without filters

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:

A Bar Chart that shows the number of staff associated to each carrier (ex...
1. NCTRACKS = 203
2. AETNA = 9
3. Trillium = 178
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

N/A

Approved

Jacob

Approved

Mary

Approved

Andrew

Approved

N/A

Approved

Kacie

Approved

N/A

Kristen

Approved

N/A

Approved

Sydneigh

Approved

N/A

Submitted

Approved

Charles

Approved

N/A

Joseph

Approved

Submitted

Approved

Jazz

Approved

N/A

Submitted

Approved

Rachel

Approved

Submitted

N/A

Approved

Jeffrey

Approved

N/A

Approved

Gayle

Approved

Sarah

Approved

N/A

Approved

Hannah

Submitted

Needed or Possibly Needed

N/A

Submitted

Needed or Possibly Needed

N/A

Needed or Possibly Needed

Heather

Approved

Submitted

Approved

N/A

Approved

N/A

Submitted

Jason

Approved

Denied

Approved

Denied

Approved

Ana

Approved

Denied

Approved

Angela

Approved

Submitted

Approved

Submitted

Approved

Karimah

Approved

Submitted

Approved

Susan

Approved

Submitted

Approved

N/A

Submitted

Approved

N/A

Lauren

Approved

Submitted

Approved

Sarah

Approved

Submitted

Approved

N/A

Aimee

Approved

Richard

Approved

Submitted

Approved

Rebeca

Approved

N/A

Approved

Tashema

Approved

N/A

Submitted

Beth

Approved

Submitted

Approved

Submitted

Approved

N/A

Approved

Brittany

Approved

N/A

Submitted

Brandon

Submitted

Needed or Possibly Needed

N/A

Needed or Possibly Needed

N/A

Needed or Possibly Needed

Josette

Approved

Submitted

Approved

Kristina

Approved

Kelly

Approved

Kaitlyn

Approved

N/A

Amanda

Approved

Submitted

Approved

Submitted

Approved

Meredith

Approved

N/A

Approved

Rachel

Approved

Denied

Approved

Amanda

Approved

Submitted

Approved

Alyson

Approved

N/A

Approved

Christopher

Approved

Submitted

Approved

Submitted

Approved

N/A

Ayrien

Approved

Ross

Approved

N/A

Approved

Sean

Approved

N/A

Approved

N/A

Submitted

Approved

Glenda

Approved

N/A

Approved

Ashley

Approved

Breana

Approved

N/A

Approved

Shante

Approved

N/A

Approved

Elizabeth

Approved

N/A

Approved

Lani

Approved

Submitted

N/A

Approved

N/A

Approved

Thanks for any ideas you can share

Ashley

Need Help

Anonymous
3 years ago
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

8 Replies

Ashish_Mathur
Super User
3 years ago
Hi,

You may download my PBI file from here.

Hope this helps.
- Anonymous
  Not applicable
  3 years ago
  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
    Super User
    3 years ago
    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.
Anonymous
Not applicable
3 years ago
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:
I opened query editor and duplicated my original table
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)
I then highlighted the ID and Name fields, the 'unpivoted other columns'
This left me with the desired 4 columns as shown in the sample pbx.
Thanks again Ashish!
dcrosseto
Resolver II
3 years ago
Hi Anonymous ,

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.
Anonymous
Not applicable
3 years ago
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
  Resolver II
  3 years ago
  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.
  - Anonymous
    Not applicable
    3 years ago
    Thanks for taking a look.

Forum Discussion

How to query multiple columns for data without filters

8 Replies

Related content

Sequential Order column based on multiple criteria

Multiple line and clustered columns chart

Summarize Multiple Columns

How to include multiple columns in Activator

Conditional Column with Multiple Conditions

Category	Count_Approved
AETNA	123
BCBS	98
Trillium	200