Forum Discussion

lukasjar's avatar
lukasjar
Resolver I
6 years ago

Manual Query, if no match = other

Hello community.

I have two different sources with [location] and [location2] with different amount of distinct values that i add in a relationship in MachineId. As you can see below there are just two matches to my excel file data (MachineId) and a large difference in DistinctCount.

1. What I would like is to write a own Query in QueryEditor that places the values that dont have a match on Location/Location2 with MachnieId into "Others". 182-15 = 167 Location should be put in 1 position called Others. I can, if necessery remove my "MachineId" file and write the MachineId in the Query as well.

 

 

 

 


2. When I add location and location2 in the same table in Power BI Desktop it results in an error. I do not understand why this is happening. Here are the relationships, via Machnie Id.

Need Help

4 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    Do Merge Queries as New using ScrapData and MachineId (in that order). Choose Join type as Left Anti. You can remove the join column. Remove all columns except location. Then repeat this for your other table. Finally Do a combine tables on these two, followed by a distinct.

     

    • lukasjar's avatar
      lukasjar
      Resolver I
      6 years ago

      The combined table is done in the same way being: Merge new query, Left Anti and remove all columns but Location?

      And how is the distinct done?

       

      • artemus's avatar
        artemus
        Microsoft Employee
        6 years ago

        1. Yes, remove all columns but location.

         

        2. Click the column and choose: Remove Rows -> Duplicates