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- Hou...
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"

     

     

     

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"