Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

Need Help
  • sanimesa's avatar
    sanimesa
    6 years ago

    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"

     

     

     

6 Replies

  • sanimesa's avatar
    sanimesa
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    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"

     

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    I will show you another way:

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

    Select all the columns >unpivot the columns:

    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:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly

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

     

     
    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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.

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Yes. See the code below. It turns this:

     

    into this:

    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"