column headers as a slicer

[Julier]

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

[PhilipTreacy · Accepted solution]

@Julier 

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

[Julier]

Worked like a dream Thank you !

[ralf_anton]

...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
RenameColumns

Oder 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"

[Jai-Rathinavel]

@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,
Jai

[Julier]

Hi, sorry, I dont have drop box

[ralf_anton]

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"