Forum Discussion
column headers as a slicer
Hello, I have my data in power query as below which i have a chart and a table visual working from this, i want to be able to slice the table and the chart with a slicer using the column headers, i have tried duplicating my table of data and unpivoting the columns to just have the headers however this will not link to my chart or table visual to work as a slicer, any help would be much appreciated.
| HQQP | FBDC | SFFG | MPG | Month | Sort | Target | |||
| 6664 | 6183 | 9441 | 4930 | September | 9 | 3000 | |||
| 6506 | 6444 | 8195 | 4740 | October | 10 | 3000 | |||
| 6144 | 6051 | 8974 | 4788 | August | 8 | 3000 | |||
| 6302 | 6243 | 9418 | 5671 | July | 7 | 3000 | |||
Download this example PBIX file
So what you are trying to do is only show the columns of data selected in the slicer?
You can use a Field Parameter for this.
From the Modelling menu -> New Parameter -> Fields and add the Fields (columns)
make sure the check box 'Add slicer to this page' is checked and click Create
Create a table and add the parameter you just created, use the slicer to display the columns you want
Regards
Phil
6 Replies
Download this example PBIX file
So what you are trying to do is only show the columns of data selected in the slicer?
You can use a Field Parameter for this.
From the Modelling menu -> New Parameter -> Fields and add the Fields (columns)
make sure the check box 'Add slicer to this page' is checked and click Create
Create a table and add the parameter you just created, use the slicer to display the columns you want
Regards
Phil
Worked like a dream Thank you !
Hallo Julier,
hattest Du Dir das so vorgestellt?
Vor dem Transponieren müssen die Überschriften noch heruntergesetzt werden.
Der M-Code dazu wäre dieser:
let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Tiefer gestufte Header" = Table.DemoteHeaders(Quelle),
#"Transponierte Tabelle" = Table.Transpose(#"Tiefer gestufte Header")
in
#"Transponierte Tabelle"Julier Please provide us with a sample file so that I can make some changes and send it over via this thread.
You can upload the sample file to a dropbox and share the link over here.
Thanks,
JaiHi, sorry, I dont have drop box
...ich denke, so herum wäre es Dir vielleicht lieber:
Der M-Code hierfür:
let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
OrgTopics = Table.ColumnNames(Quelle),
TopicDown = Table.DemoteHeaders(Quelle),
NewTopics = Table.ColumnNames(TopicDown),
NewNames = List.Zip({NewTopics,OrgTopics}),
RenameColumns = Table.RenameColumns(TopicDown,NewNames)
in
RenameColumnsOder lieber so?
...dann diesen M-Code:
let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Tiefer gestufte Header" = Table.DemoteHeaders(Quelle), #"Transponierte Tabelle" = Table.Transpose(#"Tiefer gestufte Header"), #"Sortierte Zeilen" = Table.Sort(#"Transponierte Tabelle",{{"Column2", Order.Descending}}), #"Höher gestufte Header" = Table.PromoteHeaders(#"Sortierte Zeilen", [PromoteAllScalars=true]), #"Umbenannte Spalten" = Table.RenameColumns(#"Höher gestufte Header",{{"Monat", "Attribut"}}) in #"Umbenannte Spalten"
