Slicer help

[Noxqss38242]

Is there a way to have the slicer select the info horizontally?

I have trailer group numbers:

Groups Body Rear Brakes Electrical
185385 - 185584 700 300 900 500
633753 - 633812 350 900 200 1000
ETC...

Can I put in a slicer to select groups as well as the titles IE: Body, Rear, Brakes, Electical?
So one slicer is vertical info of selecting the groups and another slicer to select the item listed at the top of each column?

 

[sandy666]

refresh thread and re-read post #9

 

[Noxqss38242]

Very novice here, PQ editor?
Unpivot?

Hmm...do you know of a youtube clip showing this?

 

[sandy666]

(sorry but I don't know any youtube movies about that)
change your source table to be an Excel Table ( Ctrl T )
then click on this table and from the ribbon Data - From Table
it will open Power Query Editor
select column Groups (just click on the header)
there on the ribbon find Transform - Unpivot Columns and select Unpivot other columns
Close&Load and choose as connection

 

[Noxqss38242]

Okay, I found how to do the power query, I then went to the tab "transform" and selected "unpivot columns". It then highlighted to the right in green "ABC Attribute" and ABC Value"

ABC Attribute lists down "Groups"
ABC Value lists down the specific groups.

I then go to the home tab
and far left has "close and load" or the arrow below that has
"close and load"
"close and load to..."

I chose "close and load to..."
clicked on "only create connection"

Do I click on "add this data to the data model"? I didn't mark that box.

I re-read #9

Followed instructions and created a pivot table using external data source.
Chose the query table (I believe) there are 2 tables to choose from? Table 1 = Connection Only //// Table 2 = 16 rows loaded, I chose Table 1.

From there the list is:
Attribute
Body
Frame
Etc...

"Group" is not in the PivotTable Field to choose to place in the Rows.
I placed Attribute in the Rows and it shows "Groups" I put Attribute in the Values section and it says "16" as there are 16 groups.
I placed Values in the Rows and it shows the specific "grouping of trailers".

Not sure where I'm going wrong.

 

[sandy666]

Do I click on "add this data to the data model"? I didn't mark that box.

very well !!!!

after unpivot you should have three columns: Groups, Attribut and Values
Groups into the ROWS, Values into the ROWS
Attribute as slicer and Groups as Slicer

 

[sandy666]

Could you tell me what you did after : Insert - Pivot Table ?

should be

 

[Noxqss38242]

After I click on "unpivot columns" order from left to right is:
Trailer, Body, Rear, Brakes, Inflation, Electrical, Suspension, Frame, Misc., Attribute, Value

Groups disappeared.

I then close and load as connection only.

Clicked on tab "insert"
Clicked on Pivot table
clicked on "use an external data source"
Chose connection (Query - Table 1)
Clicked open

Then only fields to chose from are:
Attribute
Body
Brakes
Frame
Inflation
Suspension
Misc
Rear
Trailer
Electrical
Value

So the "groups" disappeared...

 

[sandy666]

Groups disappeared.

you lost: select column Groups then Unpivot other columns

select column Groups (just click on the header)

and after that you should have

 

[Noxqss38242]

Hey now! Much love! One simple mistake of just hitting the "unpivot columns" and not choosing the drop down "unpivot other columns". This works perfectly! Sorry you had to put up with me for so long...

 

[Noxqss38242]

From this data/slicer though...can you add a pivot chart? I click on the pivot table and add pivot chart but the data is blank? I'm used to it showing the data already once I do that