Showing specific columns respective to Rule_ID(Dynamic change in table visualization)

[Rahul_001]

Sample Source Data ::

1. Excp_Col_Metadata

ID	Rule_ID	Version	Exception_Column_Name	Excp_col_index	Is_Active	Updated_Timestamp	Updated_by
1	1001	1	Customer_ID	1	Y
2	1001	1	Name	2	Y
3	1001	1	Email_ID	3	Y
4	1001	1	Address	4	Y
5	1001	1	Country	5	Y
6	1002	1	Instrument_ID	1	Y
7	1002	1	Asset_class	2	Y
8	1002	1	Email ID	3	Y
9	1002	1	Address	4	Y

      2) Exception Data

Rule_ID	Rule_Run_Date	EXCP_ID	Excp_Col_Value_1	Excp_Col_Value_2	Excp_Col_Value_3	Excp_Col_Value_4	Excp_Col_Value_5	Updated_Timestamp	Updated_by
1001	15-Jan-25	111	1111	Naruto	Naruto@gmail.com	Pune	India	25-Jan-25	xyz
1001	15-Jan-25	112	1112	Sasuke	Sasuke@gmail.com	Pune	India	25-Jan-25	xyz
1001	15-Jan-25	113	1113	Itachi	Itachi@gmail.com	Pune	India	25-Jan-25	xyz
1001	15-Jan-25	114	1114	Madara	Madara@gmail.com	Pune	India	25-Jan-25	xyz
1001	15-Jan-25	115	1115	Gara	Gara@gmail.com	Pune	India	25-Jan-25	xyz
1002	11-Jan-25	116	1115	Maxx	Maxx@gmail.com	Pune	India	25-Jan-25	xyz
1002	11-Jan-25	117	1116	Lisy	Lisy@gmail.com	Pune	India	25-Jan-25	xyz
1002	11-Jan-25	118	1117	Tatyavinchu	Tatyavinchu@gmail.com	Pune	India	25-Jan-25	xyz

Expected Output::

As per Rule ID = 1001

Customer_ID	Name	Email_ID	Address	Country
1111	Naruto	Naruto@gmail.com	Pune	India
1112	Sasuke	Sasuke@gmail.com	Pune	India
1113	Itachi	Itachi@gmail.com	Pune	India
1114	Madara	Madara@gmail.com	Pune	India
1115	Gara	Gara@gmail.com	Pune	India

As per Rule Id = 1002

Instrument_ID	Asset_class	Email ID	Address	Country
1115	Maxx	Maxx@gmail.com	Pune	India
1116	Lisy	Lisy@gmail.com	Pune	India
1117	Tatyavinchu	Tatyavinchu@gmail.com	Pune	India

Hi all,

 I am new to PowerBi, We have above requirement to visualise expected output in table visual as per slicer selection of Rule_ID(Note:: Please consider multiple Rule_ID). I have tried some transformation in power query and able to view columns we needed. Now I am facing the issue, How to visualise as per Rule_ID as it is changing column.

Below I am sending M code of each source table ::

1. Exception Data

let

    Source = Excel.Workbook(File.Contents("SMPL.xlsx"), null, true),

    #"Metadatda value_Sheet" = Source{[Item="Metadatda value",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(#"Metadatda value_Sheet", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Rule_ID", type text}, {"Rule_Run_Date", type text}, {"EXCP_ID", type text}, {"Excp_Col_Value_1", type text}, {"Excp_Col_Value_2", type text}, {"Excp_Col_Value_3", type text}, {"Excp_Col_Value_4", type text}, {"Excp_Col_Value_5", type text}, {"Updated_Timestamp", type text}, {"Updated_by", type text}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Rule_ID", "Rule_Run_Date", "EXCP_ID", "Updated_Timestamp", "Updated_by"}, "Attribute", "Value"),

    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Unpivoted Columns", {{"Attribute", each Text.BetweenDelimiters(_, "_Value_", " "), type text}}),

    #"Added Custom" = Table.AddColumn(#"Extracted Text Between Delimiters", "RuleID_colIndex", each [Rule_ID]&"_"&[Attribute]),

    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"RuleID_colIndex"}, #"Metadata column", {"RuleID_colIndex"}, "Metadata column", JoinKind.Inner),

    #"Expanded Metadata column" = Table.ExpandTableColumn(#"Merged Queries", "Metadata column", {"Exception_Column_Name"}, {"Exception_Column_Name"}),

    #"Pivoted Column" = Table.Pivot(#"Expanded Metadata column", List.Distinct(#"Expanded Metadata column"[Exception_Column_Name]), "Exception_Column_Name", "Value"),

    // Get all column names except the grouping columns

    AllColumns = Table.ColumnNames(#"Pivoted Column"),

    GroupingColumns = {"Rule_ID", "EXCP_ID"},

    ColumnsToAggregate = List.RemoveItems(AllColumns, GroupingColumns),

    // Create a list of aggregation steps dynamically

    AggregationSteps = List.Transform(

        ColumnsToAggregate,

        each {_, (x) => List.Max(Table.Column(x, _)), type nullable text}

    ),

    // Group the table dynamically

    GroupedTable = Table.Group(

        #"Pivoted Column",

        GroupingColumns,

        AggregationSteps

    ),

    #"Filtered Rows" = Table.SelectRows(GroupedTable, each true),

    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Rule_ID", Int64.Type}, {"EXCP_ID", Int64.Type}, {"Customer_ID", Int64.Type}, {"Instrument_ID", Int64.Type}})

in

    #"Changed Type1"

2)Excp_Col_Metadata

let

    Source = Excel.Workbook(File.Contents("SMPL.xlsx"), null, true),

    #"Metadata column_Sheet" = Source{[Item="Metadata column",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(#"Metadata column_Sheet", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Rule_ID", type text}, {"Version", type text}, {"Exception_Column_Name", type text}, {"Excp_col_index", type text}, {"Is_Active", type text}, {"Updated_Timestamp", type text}, {"Updated_by", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "RuleID_colIndex", each [Rule_ID]&"_"&[Excp_col_index])

in

    #"Added Custom"

Kindly help me on this to achieve the expected output or suggest some implementation step achieve this.

[v-rzhou-msft]

Hi @Rahul_001 ,

I suggest you to try unpivot function in Exception Data table.

Unpivot columns - Power Query | Microsoft Learn

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZGxDoIwEED/pTMYWkUd3QhGjIluhOECRBqkJNAa8OvlcqSGwUHTpe+uw3tJm6aMBwFnHuOhfwTli0CEuHFOJ+IMndGtHQ73BuRjlbfNdHUxqpwQq0LCRLGwDOOLZd7XhKAE4gq9qUs7OEusKYGINeSVtIOzxIYSiAQK6MAOzhLzjyAiCkT/6unNl/rtR5/AMMxwpt+RHisn2Y8znOn3pMfKDfQIT6nyyiy3H2PZGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rule_ID = _t, Rule_Run_Date = _t, EXCP_ID = _t, Excp_Col_Value_1 = _t, Excp_Col_Value_2 = _t, Excp_Col_Value_3 = _t, Excp_Col_Value_4 = _t, Excp_Col_Value_5 = _t, Updated_Timestamp = _t, Updated_by = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rule_ID", Int64.Type}, {"Rule_Run_Date", type date}, {"EXCP_ID", Int64.Type}, {"Excp_Col_Value_1", Int64.Type}, {"Excp_Col_Value_2", type text}, {"Excp_Col_Value_3", type text}, {"Excp_Col_Value_4", type text}, {"Excp_Col_Value_5", type text}, {"Updated_Timestamp", type date}, {"Updated_by", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "Data_Index"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Rule_ID", "Rule_Run_Date", "EXCP_ID", "Updated_Timestamp", "Updated_by", "Data_Index"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Excp_Col_Value_","",Replacer.ReplaceText,{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", Int64.Type}, {"Value", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Excp_col_index"}})
in
    #"Renamed Columns"

New Table looks like as below.

Then create [Keyword] column by dax in two tables.

KeyWord = 'Exception Data'[Rule_ID] * 100 + 'Exception Data'[Excp_col_index]

KeyWord = Excp_Col_Metadata[Rule_ID] * 100 + Excp_Col_Metadata[Excp_col_index]

Relationship:

Filter [Rule ID] not show blank.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rahul_001]

Hi @v-rzhou-msft ,

   I tried your way its too easy to implement and i achieved it, but however business wants in table visual as per their need. 

Thanks,

Rahul Tatar

[v-rzhou-msft · Accepted solution]

Hi @Rahul_001 ,

 As DataNinja777 mentioned before, Field parameter is a good way. However it needs us to choose the column name manually after we filter Rule ID.

As far as I know, I suggest you to use matrix, because it is the best way to hide and show columns dynamiclly.

If you still need to create table visuals, you can try this way.

Add an Index column in 'Exception Data' table. Then create five measures for these columns.

Customer_ID = 
VAR _Indexlist = VALUES(Excp_Col_Metadata[Excp_col_index])
RETURN
IF(1 IN _Indexlist,CALCULATE(SUM('Exception Data'[Excp_Col_Value_1])))

Name = 
VAR _Indexlist = VALUES(Excp_Col_Metadata[Excp_col_index])
RETURN
IF(2 IN _Indexlist,CALCULATE(MAX('Exception Data'[Excp_Col_Value_2])))

...

Result is as below.

We can find that it couldn't hide the column header for the empty column.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777]

Hi @Rahul_001 ,

To achieve dynamic column visualization based on Rule_ID selection in Power BI, you need to ensure that your data model is correctly structured. The two key tables involved are the transformed Exception Data table, which contains dynamically created exception columns based on Rule_ID, and the Excp_Col_Metadata table, which provides metadata mapping between Rule_ID and exception column names. These tables should be linked using Rule_ID to establish a proper relationship.

Once the data is structured, you need to create a slicer using the Rule_ID column from the Exception Data table. This will allow users to filter the table dynamically. Since Power BI does not support fully dynamic columns, a workaround using DAX measures can be applied. You can create a measure that retrieves the relevant column names based on the selected Rule_ID:

SelectedColumns = 
VAR SelectedRule = SELECTEDVALUE('Exception Data'[Rule_ID])
RETURN 
    CONCATENATEX(
        FILTER('Excp_Col_Metadata', 'Excp_Col_Metadata'[Rule_ID] = SelectedRule),
        'Excp_Col_Metadata'[Exception_Column_Name], ", "
    )

After setting up the slicer and measure, you can add a table or matrix visual in Power BI. Drag EXCP_ID as the row identifier and include all possible exception columns (such as Customer_ID, Name, Email_ID, etc.) in the values section. The slicer selection will control which columns are visible by filtering out irrelevant ones dynamically.

An alternative approach is using Field Parameters, a feature in Power BI that allows dynamic column selection. You can create a field parameter containing all relevant columns and use a slicer to control visibility based on Rule_ID. This method provides a more user-friendly and flexible approach.

The expected behavior is that when Rule_ID = 1001 is selected, only the columns {Customer_ID, Name, Email_ID, Address, Country} are visible. When Rule_ID = 1002 is selected, only the columns {Instrument_ID, Asset_Class, Email_ID, Address, Country} are displayed. This approach ensures that the table dynamically adjusts based on user selection, providing a flexible and interactive visualization in Power BI.

Best regards,

[Rahul_001]

Hi @DataNinja777 ,

Could you pls provide implementation step?

thanks

[DataNinja777]

Hi @Rahul_001,

To dynamically visualize the exception data based on Rule_ID selection in Power BI, we need to structure our data model correctly and use either DAX measures or Field Parameters to achieve the expected output.

Step 1: Establish Data Model Relationships
Ensure that the Exception Data table and the Excp_Col_Metadata table are properly related. The Exception Data table contains actual exception values that have been transformed into separate columns for each exception type. It includes key fields such as Rule_ID, EXCP_ID, and dynamically generated exception columns. The Excp_Col_Metadata table maps Rule_ID to relevant exception column names, helping determine which columns should be displayed for each rule. These tables should be connected through the Rule_ID field to enable filtering.

Step 2: Use a Slicer for Rule_ID Selection
To allow users to dynamically switch between different rule sets, add a slicer using the Rule_ID field from the Exception Data table. This slicer will act as the control that determines which exception columns should be displayed in the table visual.

Step 3: Create a Measure to Dynamically Filter Columns
Since Power BI does not support fully dynamic columns within visuals, we need to create a DAX measure that helps filter and display only the relevant columns based on the selected Rule_ID. The following measure, SelectedColumns, returns a comma-separated list of columns relevant to the selected Rule_ID:

SelectedColumns = 
VAR SelectedRule = SELECTEDVALUE('Exception Data'[Rule_ID])
RETURN 
    CONCATENATEX(
        FILTER('Excp_Col_Metadata', 'Excp_Col_Metadata'[Rule_ID] = SelectedRule),
        'Excp_Col_Metadata'[Exception_Column_Name], ", "
    )

This measure extracts the corresponding exception column names for the selected Rule_ID, allowing us to filter the data dynamically.

Step 4: Create a Table Visual
Next, insert a table visual in Power BI. Add EXCP_ID as the row identifier since it serves as the unique key for each exception record. Then, add all possible exception columns such as Customer_ID, Name, Email_ID, Address, Country, Instrument_ID, and Asset_Class. While all columns are added to the visual, the slicer selection will ensure that only the relevant columns are displayed based on the selected Rule_ID. When Rule_ID = 1001 is selected, the table should show columns such as Customer_ID, Name, Email_ID, Address, and Country. When Rule_ID = 1002 is selected, the table should update to display Instrument_ID, Asset_Class, Email_ID, Address, and Country.

Step 5: Alternative Approach Using Field Parameters (Recommended)
For an even more user-friendly and flexible approach, Power BI’s Field Parameters can be used instead of a DAX measure. Field Parameters allow dynamic column selection without complex DAX filtering. To create Field Parameters, go to Modeling → New Parameter → Fields, then select all potential exception columns (Customer_ID, Instrument_ID, Name, Email_ID, etc.). Name the parameter Dynamic Columns and use it as a slicer alongside the Rule_ID slicer. This method ensures that the table visual updates dynamically based on the user’s selection, reducing the need for complex DAX calculations.

Final Result
Once implemented, this approach ensures that selecting a Rule_ID dynamically updates the table to display only the relevant columns associated with that rule. This creates a clean, flexible, and interactive Power BI report that enhances usability and provides clarity in exception reporting.

Best regards,