Forum Discussion
Table Visual drop complete fields columns from a query
I have a SQL query which return more than 100 columns, I want to create a table visual and in values how can I drag and drop complete query so that I don't have to select each column one by one as it will consume lots of time.
Then I will have a slicer which will have categories and once someone select any category the table visual should only show columns for which that category value is not null or blank
Hi Priyamxylem
nfortunately, if column names need to change on refresh, there isn’t a way to dynamically select all columns for visualization—it has to be done manually, one by one.
Alternative approaches:
Use generic column names
Instead of bringing in dynamic column names, you can assign generic names like "Column 1", "Column 2", so the structure remains consistent. This avoids the issue of columns disappearing or changing unexpectedly in visuals.Unpivot the data & use a matrix visual
If your dataset structure allows it, consider using Power Query to unpivot the columns. This will transform column names into row values, making them filterable and dynamic.After unpivoting, the dataset will have three key fields:
"Column Name" (previous column headers)
"Category" (if applicable)
"Value" (the actual data)
You can then use a matrix visual, where:
The "Column Name" field is placed in the columns section.
The "Category" field is placed in the rows (if needed).
Unpivot from PQ :Result:
Using matrix :
Note , it will help only if the data is a same data type...
The pbix is attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
3 Replies
Hi Priyamxylem
You can achieve this using Field Parameters in Power BI. As a developer, you only need to define the field parameter once, and then users can drag and drop the parameter into the table visual instead of selecting individual columns manually.
Here's how it works:
Create a Field Parameter that includes all the relevant columns.
Use the field parameter in the table visual, allowing users to dynamically select which columns to display.
Connect the field parameter to a slicer—when a user selects a category in the slicer, only the relevant columns will be displayed.
If no selection is made in the slicer, all fields in the parameter will be included in the table by default.
Additionally, behind the scenes, Field Parameters are powered by DAX. This means you don’t necessarily have to manually add all the fields when defining the parameter. Instead, you can generate the necessary DAX code using ChatGPT or another automation tool to streamline the setup process.If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Ritaf1983 thanks for quick resolution, now only problem is in my case column name are coming after parsing a JSON data whose keys are not fixed it will keep on changing I have to create a Field paramter such that it include all the table column except name column.
I tried with chatgpt but whatever DAX they provided didn't work
Hi Priyamxylem
nfortunately, if column names need to change on refresh, there isn’t a way to dynamically select all columns for visualization—it has to be done manually, one by one.
Alternative approaches:
Use generic column names
Instead of bringing in dynamic column names, you can assign generic names like "Column 1", "Column 2", so the structure remains consistent. This avoids the issue of columns disappearing or changing unexpectedly in visuals.Unpivot the data & use a matrix visual
If your dataset structure allows it, consider using Power Query to unpivot the columns. This will transform column names into row values, making them filterable and dynamic.After unpivoting, the dataset will have three key fields:
"Column Name" (previous column headers)
"Category" (if applicable)
"Value" (the actual data)
You can then use a matrix visual, where:
The "Column Name" field is placed in the columns section.
The "Category" field is placed in the rows (if needed).
Unpivot from PQ :Result:
Using matrix :
Note , it will help only if the data is a same data type...
The pbix is attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
