Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Ask about query

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

General Comment

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.