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