Advanced Slicer Help Please

[Scott_Appleton_HSDPT]

I have her a datafile with all the data and figures copied and pasted from a public database to maintain corporate confidentiality. My question is rather straightforward. How can I create a slicer for each of our services instead of having a tab/sheet for each service based on the data table I created? 

Our high-level services categories (below) are what I want to be able to filter on a slicer to keep the report on one sheet with the other data hidden. The three consolidated services are as follow: 

1. Valuations

2. Brokerage 

3. Consulting 

Do I need to create connection tables?? I am new at the whole power query and relational tables concept so if someone could please "dumb it down" or find a better solution that would be great. I would love to learn how to do this--if someone could please help me I would be very grateful.  Thank you!

Extra Details if needed

Best,
Scott

[#2]

Neither of those links works at all.

[#3]

https://1drv.ms/x/s!AnKXcruIUsndvHME5Ip2BdzSFIzC?e=jIYmad

[#4]

Thank you, some advice would help,

[#5]

I would like to create formula for > than or <

[#6]

@mathetes It will not let me upload attachment. I tried entering the URL for the file. What am I doing wrong/? 

[#7]

@Scott_Appleton_HSDPT 

First: Are those the real names of real people? I know you say you copied and pasted from a public database, but those look like they shouldn't be posted. So if so, please remove the links.

Second: it's not clear what you mean by slicing. What are you looking to achieve--give us a bit more clarity on the results desired.

Third: It's possible that the FILTER function might work for you ( see a good intro here in this YouTube video: https://www.youtube.com/watch?v=9I9DtFOVPIg ). Watch it: maybe you can resolve your own issue with that function.

[#8]

This link worked https://1drv.ms/x/s!AnKXcruIUsndvHME5Ip2BdzSFIzC?e=jIYmad

[#9]

What I'm trying to do is either have a drop down or slicer on one main page that will allow selection of all our three main services that will dynamically update the data upon selection.

I would like to keep it in the slicer format as the ones I have in each tab, BUT...instead of having a tab for each service. **I WANT TO consolidate the subservices into three main services: **

1. consulting
2. Brokerage
3. Valuations

Perhaps…. break the main clean data table into smaller tables and connect them with PWR Query/ PWR PIVOT to form relational data table connections??? Would need help/guidance on this one please.

Regarding the sales rep names, they have been randomly made up using some website name generator. I will resort to generic initials or names colors moving forward. To reduce any confusion. Thank you again.

Thanks Everyone, I look forward to reading your replies as I like learning new ways of doing things!!

Best,

Username

[#10]

@Scott_Appleton_HSDPT 

I don't know about other potential helpers here, but I am not at all sure how to navigate the workbook(s) you've shared. 

Normally (to the extent there is a "normal"), when someone comes seeking help in slicing or filtering, I would expect to see a single large table of data that will serve as the source. That's not the case, not at all, with what you've posted.  There are an "Original Data Table" and a "Cleaned Data Rep Prod Index" tab... what is one to make of them? There are also multiple tabs, labelled in various ways--almost as if they've already by separated out as you desire.

So I for one would appreciate an explanation of how all of this relates to the desired outcomes you've already described. It's no doubt totally clear in your own mind, but I think you're taking for granted that it will be obvious to all of us seeing it for the first time.

[#11]

@mathetes What I THINK he is looking for is: Presently there is a master table with many COLUMNS of data. The sub tabs break the table up and do filter and such for specific COLUMNS. Instead of have many tabs to individually show individual columns he wants to know if there is a SLICER for columns. Is that basically right @Scott_Appleton_HSDPT ?
So I don't know of any good tricks to do that using Pivot Tables and far from an expert with Power Query. So the Only solution I could come up with would be using cell formulas like INDEX and FILTER and used some Lambda and its helpers. Is this something close?  Look at the Master Lookup tab

[#12]

@mtarler yes you were absolutely correct you nailed it

[#13]

@nirupatel23yahoocom ???

[#14]

Wow impressive work. Thank you. It's exactly what I was looking for. Neat idea.

[#15]

This would be perfect except that when I change the dropdown to another category the data errors out. Any solution? Thank you so much!

[#16]

@Scott_Appleton_HSDPT I'm not sure what issue you are having. It is working on myside. You did tag Microsoft 365 but I suppose I should verify that your Excel is up to date. Cell A6 should have a LET() function but if you see something like    xlfn___     in front of it that means your version of Excel is not up to date and doesn't support that function.  Here is a good image of how my version looks:

[#17]

@mtarler   see the xlfn was the issue it's in the formula. What should I do? Thank you for your speedy response.

[#18]

It says Im all up to date. Enterprise version of 365 Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20936) 64-bit

[#19]

@mtarler 

From my end

[#20]

interesting you seem to be just a little behind since you DO have LET() but don't have LAMBDA(). Go to File, Account, and that is where you can see what build of Excel you have. AND there is a button Update Options and you can try the Update Now option.