Search multiple columns for unique data elements to ultimately report on

Hi, Thanks for the reply and suggestion. This is an interesting feature that I did not know about.

After playing with it, I see that it combines or pivots my 5 columns into one column by duplicating each row. This would be perfect for the example provided; however, in my case, it takes one patient's account and duplicates it for every physician on the account. In my case the same physician can be in multiple column on the same patient record. Now when I pivot, it will duplicate the patient’s charges for every field that physician might be listed in. In one example, my physician is listed as the attending, admitting and surgeon on the same record, so my financials are now 3 times higher. I need to find if the physician is listed anywhere with-in these fields and return only the original "rows" or record’s data for an accurate look at that patient's financials.

Also I noticed that once the “unpivot column” is created, those 5 fields are removed. I already have other financial cuts based on those 5 columns. I have a dashboard create for each of those physician types already in my report. This way the user can view the physician business as just the attending, admitting, referring, surgeon or primary care physician. I now need a separate cut that looks at all those fields together and returns financials if they exist in any of those fields without duplicating.

I know this is a big ask and I appreciate you helping me learn. Currently I have this report built in Power Pivots, where I have two databases. one with non-reoccurring data that list each patient individually by record/row and it has each of these 5 physician types in each record/ row. Then I have a dataset similar to what the "unpivot column" achieves, multi-occurring financials. I have a relationship built to allow me to select the physician if it exists in the reoccurring data, but returns financials from the other dataset, as to not duplicate financials.

I could recreate this in the Power BI, but was hoping to learn a better way to manage data and eliminate the extra data if possible.

Thanks again for any help you can offer, I’m learning more every day!

Terry