Dynamic Table visualization

[Rahul_001]

Source ::

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.

[rajendraongole1]

Hi @Rahul_001  - You're on the right track with your Power Query transformations, but the key challenge here is dynamically showing the appropriate columns based on the Rule_ID selection in a slicer. Since Power BI does not support dynamic column selection in visuals natively, 

alternatively, If you need to use a Table visual instead of a Matrix you can create below measure:

SelectedColumns =
VAR SelectedRuleID = SELECTEDVALUE( Excp_Col_Metadata[Rule_ID] )
RETURN
CALCULATE(
MAX( ExceptionData[Value] ),
FILTER( ExceptionData, ExceptionData[Rule_ID] = SelectedRuleID )
)

Apply the measure in a Table visual, and it will dynamically show only the necessary columns.

sme suggestions from my end, Use a Matrix visual → Automatically adapts columns based on Rule_ID.Use a slicer for Rule_ID → Dynamically filters the correct data.Use Power Query to Unpivot Data → Allows dynamic filtering.This method ensures scalability when new Rule_IDs or columns are added in the future.

[Rahul_001]

Hi @rajendraongole1 ,

 thanks for the suggestion, However i got stuck looking at your dax expression means on which query step you applied Value column as there is no value column. My understanding you have tried by unpivoting value column right?.

However it will be great help if you could pls provide detailed query step or approach to outcome.

Thanks