Manual Query, if no match = other

[lukasjar]

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.

[artemus]

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]

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]

1. Yes, remove all columns but location.

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

[lukasjar]

I did not get it to work that way. However I tried doing it this way and got it to work at this time.

1. Renamed column Location2 in MachineHours to Location

2. New Append queries on ScrapData and MachineHours

3. Removed other columns then Location in the new Appended Query.

4. Removed duplicates on the new Appeneded query Location.

5. Can now link ScrapData, MachineHours and MachineId to the new Table.

One issue remains doing it this way is that I have not automated renaming or adding a column with same name or for all of the Locations that are not in my MachineId table as "Other".