Ask about query

[Anonymous]

Hi all,

When I have data like Products and Transactions, if in Transform Data, the Products is filtered by some condition, lets say there is Active column then I filtered "Yes" only, then I press confirm to close the Transform data screen, is the Transaction table after I put Relationship on Product key, will it be filtered as well  in report ? or it will have rows of Blanks product ?

If yes, how to make it filtered as well ? need to create new table with InnerJoin ? is there any other way ? 

Thanks

[Anonymous]

Hi @Anonymous ,

From my test, after filtering Product table in Power Query, the original matched value in Transaction table would be changed into blank:

You could use the following formula to creata a new table:

Table = FILTER('Transactions', [ID] in VALUES(Products[ID]))

Or apply the below measure to Filter pane like this:

Measure =
IF ( MAX ( 'Transactions'[ID] ) IN VALUES ( 'Products'[ID] ), 1, 0 )

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

If you are filtering the Product table with Active ="Yes" then you need to do the same on the transaction table as well in Power Query, if not, once the relationship is created, you see blank values for missing products.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

Hi @Fowmy 

But in my Transaction table there is only Product Id,  the Active is in Product table, how to put a relationship to filtered some field in other table in Power Query ?

Thanks,