Need M Script to sum values from joined/merged queries

Question

Hi guys here's 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| Vehicle1CapaScore | 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 queries). However, I have no idea how I would get the 37 separate merged queries to sum and populate a score for the Vehicle1CapaScore 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.

@MikeHoney I'm curious, how would the PBI vs PQ change how you'd answer? — Carl Walsh
@CarlWalsh - the terminology is different e.g. PBI Edit Queries vs Excel Power Query ribbon. — Mike Honey

Mike Honey Mike Honey · Accepted Answer · 2016-04-28T07:19:34

I would Unpivot both tables e.g. for Table 1:

select ID# column
choose Transform / Unpivot columns / Unpivot Other Columns

I would repeat that for Table 2, selecting the Capability column. This will give you more useful data structures that you can Merge in one step. I would use a Group By on the result to Sum the Table 2 / Vehicle Score values across the Capabilities.

I'm sure how useful your output format would be, but if that's what you really need you can probably produce it from the Merged result using 2 Pivot steps (on Capability # and Vehicle #).

Need M Script to sum values from joined/merged queries

1 Answers