1

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.

Improve this question
3
  • Is this for Power BI Desktop or Power Query Excel Add-In? Commented Apr 28, 2016 at 7:12
  • @MikeHoney I'm curious, how would the PBI vs PQ change how you'd answer? Commented Apr 28, 2016 at 22:48
  • @CarlWalsh - the terminology is different e.g. PBI Edit Queries vs Excel Power Query ribbon. Commented Apr 29, 2016 at 0:13

1 Answer 1

Reset to default
2

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 #).

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Okay, after some value replacing, filtering for blanks, expanding structured columns, re-pivoting and then merging back to the original "Table 1" (the real data is significantly more complicated. This approach worked! (I'm fairly impressed by PowerBI/Queries GUI being able to do all this with no custom code).
However, there is one small caveat to my original question that I wanted to create a case statement for.... basically, if the score for a vehicle on ANY capability required on an ID# is 0 then the subsequent score would be zero. For example, ID#97 requires "Run"ing. Vehicle 2&3 score 0's for Run in Table 2. Ergo, even tho Vehicle 2 & 3 get a score for crawling and walking the 0 in run makes their total score zero for ID#97. How and Where could I integrate that case into this process?
@MarkDavidGaal that sounds like a new question, want to ask that as a new StackOverflow post? If you're able to include some queries and source data that we can run, it would help us. Don't forget to mark this answer as the solution to your question!
@MarkDavidGaal - yes PBI Queries / Power Query is a great tool. Its a shame it is so hidden in PBI and Excel 2016. I prefer to build my queries in Excel where you have 10x better tools to test and review the results. You can copy and paste Query definitions between Excel and PBI.
@MarkDavidGaal for your zero requirement, I would add a Min calc to the Group By to find Vehicles with a 0. Then I'd Add a Column and write a simple if statement e.g. if [Min Value] = 0 then 0 else [Sum Value]
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.