Forum Discussion
Consolidating and manipulating data from different workbooks into one workbook
There are two different reports downloaded into Excel. Each has a different layout. Both have the same information that needs consolidating; however, extra rows are needed in the first report and the data cells in the 2nd report are non-adjacent. I've read and watched numerous tutorials on Power Queries and the like, but simply cannot figure it out. Could you, not just provide the source for the solution, but walk me thru this process, please? I've attached a detailed image of what I'm trying to do and the expected result. Thank you very much!!
11 Replies
I added a Merge Query named Test1PQ+TestResultDataSet as a variant to the Pivot View which shows all rows in the Test1 Power Query sheet + all rows of the Test Merged Source which table name is "TestResultDataSet" result shown in image below.
At first the Merge Query wouldn't match when I selected the Full Outer join (all rows from both)
so I checked the Test Merge Source data set sheet tab and found that there were excel ctrl+enter characters in the SC Number column so I used the formula:
=SUBSTITUTE(TestResultDataSet[@[SC Number]],CHAR(10),)
which replaces CHAR(10) with nothing ( ctrl+enter is CHAR(10) )
Note: I did not remove the duplicated SC Number columns from the resulting merged data set to verify, confirm, and show they match (You can choose to remove whichever column you wish).
updated File attached below
Let me know if you have questions or clarifications.
cheers
Step 1 DATA PREP:
Your Data Set:
Converted it to this:
Final Data Set:
Added Column 1 with formula: =LEFT(B2,FIND(" ",B2,1)-1)
Step 2 CREATED DIM TABLES:
Control Family DIM Table:
Step 1 Formulas:
NormalizedControlNumber =IFERROR(LEFT(C7,FIND("(",C7,1)-1),C7) ControlNumber =UNIQUE([Eastcoastbre_Instructions.xlsx]NormalizedRSR1!A2:A21) Control Family =VLOOKUP(C7,[Eastcoastbre_Instructions.xlsx]NormalizedRSR1!$A$1:$I$21,2,0) Step 2 Formulas:
Normalize Control Number =UNIQUE(B7:B11) Match Control Family Name =VLOOKUP(F7,$B$6:$D$11,3,0) Final Control Family DIM Table:
SC Number DIM Table:
Made change:
Formulas for steps 1-2
Step 1 =TRIM(SUBSTITUTE(C6,CHAR(160),)) Step 2 =IFERROR(LEFT(C6,FIND("(",B6,1)-1),B6) Formulas for steps 3-6
Step 3 =UNIQUE(A6:A25) Step 4 =LEFT(H6,FIND(".",H6,1)-1) Step 5 =INDEX($A$5:$E$25,MATCH(H6,$A$5:$A$25,0),4) Step 6 =INDEX($A$5:$E$25,MATCH(H6,$A$5:$A$25,0),5) Final SC Number DIM Table:
Did a Power Query to the Final Data Set
Split the SC Number in Data Set:
From this:
to this:
Power Query Result:
Converted the Test Merge Source
from this:
To this:
Loaded the following to the Data Model:
Two DIM Tables
Power Queried Final Dataset
Test Result Source
Pivot View of Data Model:
File attached below
Let me know if you have any questions or clarifications.
cheers
- would you be able to share the worksheets involved?
Yes. I've created 2 different layouts for the Summary Report. Here are the questions that correspond with each of the 3 tabs.
1. How to consolidate this data into the appropriate cells in the Summary Report.
2. Could I use either an Outline, Function, Formula, Query, or ???
3. Can the Summary Report be linked to different sources so that it automatically updates?
Thank you!My Questions:
which column will the test results be on Original Summary Report#1? Column C Row 2?
which column will the test results be on Revised Summary Report#1? Column D adjacent to SC Number?
Your Questions:
Yes. I've created 2 different layouts for the Summary Report. Here are the questions that correspond with each of the 3 tabs.
Q 1. How to consolidate this data into the appropriate cells in the Summary Report.A 1. In the current state of the dataset as it is in your sample workbook using the Revised Summary Report#2, It looks like a Data Model would be most appropriate course to go forward with.
2. Could I use either an Outline, Function, Formula, Query, or ???A 2. (See A 1.)
3. Can the Summary Report be linked to different sources so that it automatically updates?A 3. Using Power Query + Data Model, Yes (query by folder)
I have to say, not that it's immediately helpful to you, that it's not even clear why you've converted the originals to Excel. The only apparent "value" that Excel might offer is in its ability to array things in cells that happen to be neatly organized in rows and columns. Beyond that, however, none of this seems to actually USE Excel for calculating, summarizing, cross-tabulating--the things at which Excel excels.
And (although I could be wrong about this) I can't imagine Power Query being helpful either, for the simple reason that the data aren't arrayed as tables.
So where I'm going with all that is to ask you some questions:
- WHAT is the nature of the original reports?
- From what system(s) do they come? In what kind of form(s)?
- Might it be possible to take them and convert them to tabular data (single records on a single row) SO THAT Power Query could work on them?
Original report generated from internal system in PDF. Can only convert to Word, Excel, or Word pad. Limited to generic reports and cannot customize. Need to consolidate test results from the top report in the image into the control status report. Cumbersome process. Excel was the better option.
Thank you for your assistance. I appreciate your level of effort. Take care.
