Forum Discussion
Excluding Data from the source when creating a new Group by reference table
Hi,
I have created a reference table from an existing table and i have grouped the new table accordingly. However I would like to exclude data from the source table where the column [Applicability] = "Exclude".
The idea is when I aggregate the data by grouping it, I want the above mentioned data to be excluded.
Thank you
Vijay
Hi Jayprekk
Thank you for reaching out to the Microsoft Forum Community.
As per your query regarding excluding data from the source when creating a grouped reference table.
If you wish to exclude the data in both the source table and the reference table, you can apply the filtering directly in the source table within the Query Editor.
However, if you want the data to be excluded only in the reference table but not in the source table, follow these below steps
Based on the scenario you’ve described; I’ve worked through logic using sample data to provide a clear understanding of the process. Here’s the step-by-step approach:
Load the Sample Data:
Load your data into Power BI and navigate to Transform Data.
create a Reference Table:
In the Query Pane, right-click on the source table and select Reference.
This will create a new reference table linked to the source table.
Filter the Applicability Column:
- In the newly created reference table, apply a filter to the Applicability column to exclude the rows with unwanted values.
After filtering the required field, the below is the output.
Group and Aggregate the Data:
- After filtering, use the Group By feature to aggregate the desired columns, such as summing up visit_count and Fuel Trx.
Above is the output of the group by in Reference Table.
The data in the source table remains unchanged, ensuring its integrity want to change the in-data source we can use the filter option as above. Additionally, you can disable the Enable Load option for the table if you prefer it not to appear in your data model & visuals, avoiding interference with your outputs.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
NOTE: In the question mentioned above, would you like to exclude the entire row where "Exclude" is specified in the Applicability column, or do you intend to exclude the entire Applicability column itself? Additionally, could you clarify whether the aggregation was performed in the source table or the reference table?
7 Replies
Hi Jayprekk
If you want to have the data excluded in BOTH tables, just do the filtering in the source table in the Query editor.
If you want to have the data excluded ONLY in the source table buut not in the reference table, do this:
try to create a new table in the Query Editor using right click on the source table > ReferenceThen rename the source table to any different name and disable Data Load (also with right click)
After that do your desired filtering in the newly created reference table and rename it with the source table's exact name. Now you can load the data and you will have the filtered data table as your new "source table"
Hope this helps
Cheers
Tim
Hi Tim,
I would like to source table to be as it is but to exclude some of the line items into the reference table. The snapshot below summarizes the above.
Then you should take a close look at the perfectly crafted suggestion from v-saisrao-msft because it is exactly the solution for what you are trying to achieve.
Hi Jayprekk
Thank you for reaching out to the Microsoft Forum Community.
As per your query regarding excluding data from the source when creating a grouped reference table.
If you wish to exclude the data in both the source table and the reference table, you can apply the filtering directly in the source table within the Query Editor.
However, if you want the data to be excluded only in the reference table but not in the source table, follow these below steps
Based on the scenario you’ve described; I’ve worked through logic using sample data to provide a clear understanding of the process. Here’s the step-by-step approach:
Load the Sample Data:
Load your data into Power BI and navigate to Transform Data.
create a Reference Table:
In the Query Pane, right-click on the source table and select Reference.
This will create a new reference table linked to the source table.
Filter the Applicability Column:
- In the newly created reference table, apply a filter to the Applicability column to exclude the rows with unwanted values.
After filtering the required field, the below is the output.
Group and Aggregate the Data:
- After filtering, use the Group By feature to aggregate the desired columns, such as summing up visit_count and Fuel Trx.
Above is the output of the group by in Reference Table.
The data in the source table remains unchanged, ensuring its integrity want to change the in-data source we can use the filter option as above. Additionally, you can disable the Enable Load option for the table if you prefer it not to appear in your data model & visuals, avoiding interference with your outputs.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
NOTE: In the question mentioned above, would you like to exclude the entire row where "Exclude" is specified in the Applicability column, or do you intend to exclude the entire Applicability column itself? Additionally, could you clarify whether the aggregation was performed in the source table or the reference table?Thank you v-saisrao-msft . I somehow misunderstood the initial solution shared. It works!
Thanks timalbers for the nudge in regards to the solution given
Hi there,
Apologies but maybe I was not entirely clear with my question. I have summarized it in the below diagram.
