•4 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to properly flatten my data for Power Query/Pivot Tables
Our take
Flattening data for Power Query and Pivot Tables can feel daunting, especially when working with multiple tables and varying evaluations. However, mastering this process is essential for transforming raw data into actionable insights. In this guide, we’ll break down how to flatten your data efficiently, ensuring that you can seamlessly analyze performance across teams. By the end, you’ll understand how to consolidate multiple evaluation scores into a single column, facilitating better reporting and decision-making. Let’s dive in and unlock the full potential of your data!
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]
- Having trouble with the logic on how to transfer info from one table to anotherHi Again, First, a screenshot of both tables' headers https://preview.redd.it/cukxw4bobikg1.png?width=615&format=png&auto=webp&s=69c1ceec4141b15ec691b33a4e24466520bb106a The one in black is the source. Agent's Team Lead = Sheet Name in the destination wb. The Orange is the destination, it's also in a separate wb. The problem, there are two tables on each sheet. There are a total of 4 teams. The tables are named T1_In, T1_Ex to T4_In, T4_Ex. How do I know which T? they are on? Once I have that figured out, I need to check if they are already on the table. ...If they are on the table, I have to find out which QA it is (1st, 2nd, 3rd, 4th) based on which is empty ...If not, add them to the table I can't seem to wrap my head around the logic in how to do the above. The source of the sourceTable is a Power Query which pulls from am Excel file. It only has daily updates, meaning only shows new QA from the previous report. These daily reports are not saved, but the wb is. The destination table will then become the source for other Power Queries on that wb's other sheets for daily reporting of the current state of the month. submitted by /u/Difficult_Cricket319 [link] [comments]
- How can I automatically update Power Queries with new tables/sheets as they are added to a workbook?Hi Everyone, I've finished the project for my boss and now working on my personal project y'all have helped me on using VBA. Trying to change it to Power Query and Pivot Tables now that I learned more about them. Here's my situation, I want the power queries to automatically update when new sheets/tables are added to the workbook. I did a Google search to ask for help it doesn't seem to be working. The below is the result from Google: Format Data: Ensure all sheets have identical column headers and format data ranges as Tables (Insert > Table). Get Data: Go to Data > Get Data > From File > From Workbook (or From Table/Range for a single workbook). Combine: In Power Query, filter the sheets to include only the ones needed. Expand the data. Unpivot: Select the columns that should not change (e.g., ID, Name). Right-click and choose "Unpivot Other Columns". This ensures new columns added later are automatically unpivoted. Load: Click Close & Load. The steps I've done: All data on the sheets are formatted as tables with unique names Loaded the workbook (current) Filtered 'Name' to begin with XXX_ (Tables are named XXX_Mon where XXX is a company identified and Mon is the 3 letter month) -- This step results in the two tables I'm expecting Removed 'Item', 'Kind', and 'Hidden' -- This leaves me with 'Name' and 'Daa' Expanded the Data column A dialog pops up asking what I want, so I select Agent, Score 1, Score 2, Score 3, Score 4 After clicking OK, I now have 'Name' & the above columns prefixed with Data. I selected 'Name' and 'Data.Agent' and right-clicked and picked Unpivoted Other Columns Renamed 'Attribute' to 'Eval' Renamed 'Value' to 'Score' Final Result: Name, Data.Agent, Eval, Score Click on Close & Load To... Selected Only Create Connection Click on Add this data to the Data Model This is showing all the records (50) as expected. When I add a new sheet named XXX Mar with the table name of XXX_Mar. I refresh all, but March isn't loading in to the Power Query (still showing 50 records). https://preview.redd.it/qk4yuo9dr3jg1.png?width=209&format=png&auto=webp&s=2843e8e1a8452bc65af006a75766d6f099977575 What am I doing wrong to have the query auto-add the new table? submitted by /u/Difficult_Cricket319 [link] [comments]