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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
sanimesa
Post Prodigy
Post Prodigy

@Anonymous 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
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

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
sanimesa
Post Prodigy
Post Prodigy

@Anonymous 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

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! 😊

edhans
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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