How to properly flatten my data for Power Query/Pivot Tables
Hi everyone,
You all are helping me on something in another thread (https://www.reddit.com/r/excel/comments/1qwytsd/comment/o434tjh/) but I am apparently not setting up my Power Queries correctly. So I created this thread to assist me in how to properly create a Power Query.
I am unable to upload my Excel sheet, they have things pretty locked down here.
I am going to list 2 tables, but there are actually 8. Team 1 - Internal
| + | A | B | C |
|---|---|---|---|
| 1 | Agent Name | First Evaluation | Second Evaluation |
| 2 | Ian McShane | 0% | 85% |
| 3 | Morgan Freeman | 0% | 79% |
| 4 | Robin Williams | 93% | 92% |
| 5 | Samuel L. Jackson | 84% | 93% |
| 6 | Keanu Reeves | 99% | 78% |
| 7 | Betty White | 82% | 87% |
| 8 | Denzel Washington | 85% | 91% |
| 9 | Danny DeVito | 99% | 76% |
| 10 | Jackie Chan | 90% | 93% |
| 11 | Chris Evans | 78% | 80% |
| 12 | Jennifer Lawrence | 75% | 75% |
| 13 | George Clooney | 80% | 81% |
| 14 | Emma Stone | 88% | 91% |
| 15 | Charlize Theron | 93% | 92% |
| 16 | Brad Pitt | 98% | 88% |
Table formatting by ExcelToReddit
Team 1 - External
| + | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Agent Name | First Evaluation | Second Evaluation | Third Evaluation | Fourth Evaluation |
| 2 | Ian McShane | 80% | 84% | 87% | 100% |
| 3 | Morgan Freeman | 78% | 96% | 99% | 81% |
| 4 | Robin Williams | 80% | 86% | 76% | 95% |
| 5 | Samuel L. Jackson | 86% | 91% | 78% | 94% |
| 6 | Keanu Reeves | 99% | 83% | 77% | 79% |
| 7 | Betty White | 82% | 98% | 77% | 94% |
| 8 | Denzel Washington | 78% | 91% | 80% | 77% |
| 9 | Danny DeVito | 80% | 98% | 81% | 83% |
| 10 | Jackie Chan | 97% | 77% | 88% | 84% |
| 11 | Chris Evans | 99% | 95% | 84% | 76% |
| 12 | Jennifer Lawrence | 80% | 89% | 80% | 84% |
| 13 | George Clooney | 88% | 88% | 80% | 75% |
| 14 | Emma Stone | 92% | 85% | 93% | 81% |
| 15 | Charlize Theron | 97% | 75% | 85% | 82% |
| 16 | Brad Pitt | 79% | 84% | 84% | 82% |
Table formatting by ExcelToReddit
Please note: Team 2, Team 3, and Team 4 all follow the above two tables. Each team has different agents. i.e. an agent on team 1 will not be on 2, 3, or 4. This agent on team 1 is on both Internal and External.
From the help I am getting on the other thread, I need to flatten these, I will also add a column to each table with the table's name, and that score 1 and score 2 (or score 1, 2, 3, 4) need to be converted to just 1 column but do not know how to go about that.
Could someone teach me on how to flatten this in Power Query?
Edit: Finally got the table to format correctly. I should only have 1 column of scores, with another column that says 1st,2nd,3rd,4th so that when I try to do the overview I can get it to display and calculate properly.
A screenshot of the overview can be seen in the other thread linked at the top.
[link] [comments]
Want to read more?
Check out the full article on the original site