CODEWITHSUNDEEP
powerbipowerquerym
Mark David Gaal
Mark David Gaal

Reputation: 69

Need M Script to sum values from joined/merged queries

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.

Upvotes: 1

Views: 271

Answers (1)

Mike Honey
Mike Honey

Reputation: 15027

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

  1. select ID# column
  2. 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 #).

Upvotes: 2

Related Questions