cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Create a slicer with Column Headers

Is it possible to create a slicer with column headers?

 

I would Like to create a drop down slicer that only shows header names and not values  ( Line Rate, Accessorial Rate, Hourly Rate,  Non- Hourly Rate, Total Rate)

 

Line Rate     Accessorial Rate      Hourly Rate     Non Hourly Rate    Total Rate

1.23                            2.34                                1.22                      9.90                                3.22

1.45                          4.55                                 3.44                          18.23                           4.55

3.67                        3.12                               1.89                              3.44                              6.78

 

1 ACCEPTED SOLUTION
Post Prodigy
Post Prodigy

@Csmith31 You can use power query to create this - please replace TheTable below to the name of your table. 

 

On the Advanced editor, create a blank query and use this code:

let
    Source = Table.ColumnNames(TheTable as table) as list,
    #"Converted to Table" = Table.FromList(Source, null, {"TheHeaders"})
in
    #"Converted to Table"

 

 

 

headerslicer.PNG

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi @Csmith31 ,

 

I will show you another way:

Go to query editor>right click on table name in the "Queries">"Duplicate":

Annotation 2020-05-01 095617.png

Select all the columns >unpivot the columns:

Annotation 2020-05-01 095813.png

Then "Apply and close" and back to data view,create a new table using below dax expression:

 

 

 

Slicer table = DISTINCT('Table (2)'[Attribute])

 

 

 

And you will see:

Annotation 2020-05-01 100018.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

I love power query. Always about 5 ways to get many things done. Mine took the "only use the UI approach" but with come M coding, can be done in fewer setps.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Post Prodigy
Post Prodigy

@Csmith31 You can use power query to create this - please replace TheTable below to the name of your table. 

 

On the Advanced editor, create a blank query and use this code:

let
    Source = Table.ColumnNames(TheTable as table) as list,
    #"Converted to Table" = Table.FromList(Source, null, {"TheHeaders"})
in
    #"Converted to Table"

 

 

 

headerslicer.PNG

View solution in original post

Didn't think of dropping to a list @sanimesa for this one. Much shorter. 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans Thanks! 😊

Super User III
Super User III

Yes. See the code below. It turns this:

2020-04-30 15_58_39-Untitled - Power Query Editor.png

 

into this:

2020-04-30 15_59_20-Untitled - Power Query Editor.png

You can then create a slicer with that data. You should create a reference to the original table in Power Query to start this process, then load this table as a Fields table.

 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzbDQAhCAR74dtsTh4qtRj6b0Pw7hKSDcMse1MHCzViiGbkJKjd4Y+AmaKVpJZQYfZKAi29r2xfWpZgzHvr/L9a/skDc1HEAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [LineRate = _t, AccessorialRate = _t, HourlyRate = _t, NonHourlyRate = _t, TotalRate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LineRate", type number}, {"AccessorialRate", type number}, {"HourlyRate", type text}, {"NonHourlyRate", type number}, {"TotalRate", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "Fields"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Fields"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Fields", type text}})
in
    #"Changed Type2"


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors