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

[Jayprekk]

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

[v-saisrao-msft · Accepted solution]

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? 

[Jayprekk]

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

[Jayprekk]

Hi there,

Apologies but maybe I was not entirely clear with my question. I have summarized it in the below diagram. 

[timalbers]

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 > Reference 

Then 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

[Jayprekk]

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.

[timalbers]

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.

[Jayprekk]

Hi Tim,

The thing is I am not able to add [Applicability] field in the aggregated table as it will create duplicate value for [UserID]. I am creating an aggregated table as such to build a many to 1 relationship. This is the reson why It's not clear to me on how the rows can be excluded in the aggregated table.

@v-saisrao-msft - Please let me know if I have misunderstood your initial response. 

Appreciate the support to guide me through