Custom M Script to sum values from Joined/Merged Queries

[MarkDGaal]

Hi guys heres what i'm trying to accomplish....

My data structure Table1: 

ID#| Capability.1 | Capability.2 | Capability.3| .... 37 total Capability.# columns 
97 | Crawl        | Walk         | Run         |
98 | Crawl        | null         | null        |
99 | Crawl	  | Walk	 | null	       |	

My data structure Table2: 

Capability | Vehicle1Score| Vehicle2Score| Vehicle3Score| 
Crawl      | 4            | 1            | 5            |
Walk       | 3            | 1            | 5            |
Run        | 2            | 0            | 0            |

If a ID# requires crawling, walking, and running I would like to have the scores of Vehicles 1-3 merged with the record on how well they can crawl, walk and run. For example:

ID#| Capability.1 | Capability.2 | Capability.3| Vechicle1CapaScore | Vehicle2CapaScore | Vehicle3CapaScore| 
97 | Crawl        | Walk         | Run         | 9 [4+3+2]         | 2 [1+1+0]         | 10 [5+5+0]       |
98 | Crawl        | null         | null        | 4 [4+null+null]   | 1 [1+null+null]   | 5 [5+null+null]  |
99 | Crawl	  | Walk	 | null	       | 7 [4+3+null]      | 2 [1+1+null]      | 10 [5+5+null]    |	

I've gotten as far as using merge queries (LeftOuterJoin on Table1[Capability.#] = Table2[Capability]) to bring the Table2 Capability and Vehicle Scores into Table1 for each Capability.# column (Total of 37 merged queires). However, I have no idea how I would get the 37 separate merged queries to sum and populate a score for the Vechile1CapaScore column.

If it's helpful I can write a query in SQL server to demonstrate what I'd like to accomplish; however, I'm not proficient enough in R to do the same. Please let me know if this makes sense and you can help.

[ankitpatira]

@MarkDGaal Are you after solution in R ?

[MarkDGaal]

Hi ankitpatria, thank you, but I found my solution through a combination of unpivoting, grouping, otherwise transforming, and re-merging my quieres in the query editor.

[MarkDGaal]

Here is what I would write in SQL to do the same....

select  t1.ID, t1.Capability.1, t1.Capability.2, t1.Capability.3
         ,t2.[Vehicle1Score] 
		 + case when t3.[Vehicle1Score] is null then 0 else t3.[Vehicle1Score] end
         + case when t4.[Vehicle1Score] is null then 0 else t4.[Vehicle1Score] end as [Vehicle1CapaScore]
      ,t2.[Vehicle2Score] 
		 + case when t3.[Vehicle2Score] is null then 0 else t3.[Vehicle2Score] end
         + case when t4.[Vehicle2Score] is null then 0 else t4.[Vehicle2Score] end as [Vehicle2CapaScore]
      ,t2.[Vehicle3Score] 
		 + case when t3.[Vehicle3Score] is null then 0 else t3.[Vehicle3Score] end
         + case when t4.[Vehicle3Score] is null then 0 else t4.[Vehicle3Score] end as [Vehicle3CapaScore]
from      Table1 as t1 left outer join
          Table2 as t2 on
          t1.Capability.1 = t2.[Capability] left outer join
          Table2 as t3 on
          t1.Capability.2 = t3.[Capability] left outer join
          Table2 as t4 on
          t1.Capability.3 = t4.[Capability] left outer join