Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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"
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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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"
Didn't think of dropping to a list @sanimesa for this one. Much shorter. 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes. 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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |