Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
anwilkins
Resolver II
Resolver II

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:

  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 

anwilkins_0-1665429317872.png   

 

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

anwilkins_1-1665429453154.png

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

 

anwilkins_2-1665430037881.png

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.Wellcare
JanetApproved   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  Approved
SarahApproved Approved Approved Approved     N/A   Approved  Approved
HannahSubmitted 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 ApprovedApproved
RebecaApproved   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 share

Ashley

1 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

View solution in original post

8 REPLIES 8
anwilkins
Resolver II
Resolver II

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
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
anwilkins
Resolver II
Resolver II

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:

anwilkins_0-1665433684967.png

 

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.

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:

 

CategoryCount_Approved
AETNA123
BCBS98
Trillium200

 

Hope it helped.

 

Regards,

Douglas.

Thanks for taking a look. 

dcrosseto
Resolver II
Resolver II

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.