Forum Discussion

mohit01chugh's avatar
mohit01chugh
Frequent Visitor
8 months ago
Solved

Dynamic Column Selection in a Table Visual

The objective is to implement a single, dynamic slicer that controls which specific columns from the main sales data table are displayed in an adjacent Table visual. Input Data: A single sales d...
Need Help
Show and Tell
Tips and Tricks
  • v-sgandrathi's avatar
    v-sgandrathi
    8 months ago

    Hi mohit01chugh,

     

    Thank you for sharing the screenshots. This behavior is expected, Power BI retains a column and its header in the visual even if all its cells are blank, so hiding values won’t remove the Sale header.

    To address this, you have three options:

    1.Use Field Parameters (recommended) by creating a parameter with Stock and Sale fields, adding it to your Table or Matrix, and controlling it with a slicer so only the selected field appears;

    2.Unpivot the Stock and Sale columns in Power Query, then use the Attribute field in a Matrix and control which attributes display with a slicer; 3.Set up separate visuals for Stock-only, Sale-only, and both, then toggle between them using a slicer and bookmarks.

    Field Parameters offer the best user experience, unpivoting is most model-friendly, and bookmarks are a straightforward alternative.

    Thank you.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 months ago

Hi,

This Power Query code will transform the data into a neat table which you can now load into the Data Model

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYu7CoAwDEV/RTor1Fpfo6KCs2PpIMXJ12D9f9OAMYvD4VxyiDFCxGLyp1vBYc7bEpaNjehmH/bol/16HbnzPvzPNTxJnaQqUVLlEBsgJWPOWW4BRcZcsOyAjIy5ZLkDNBlzxXIPfMZcszwABdnaBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    First2rows = Table.SplitAt(Source,2),
    Headings = List.Transform(Table.ToColumns(Table.FromRows(List.Transform(Table.ToRows(First2rows{0}), each Table.FillDown(Table.ReplaceValue(Table.FromColumns({_},{"Value"}),"",null,Replacer.ReplaceValue,{"Value"}),{"Value"})[Value]))), each Text.Combine(_,"/")),
    Custom1 = Table.FromColumns(Table.ToColumns(First2rows{1}),Headings),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Custom1, {"Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Attribute.1", type text}, {"Items", type text}, {"Items count", Int64.Type}})
in
    #"Changed Type"

Hope this helps.