Excluding Data from the source when creating a new Group by reference table

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?