Forum Discussion

Eastcoastbre's avatar
Eastcoastbre
Copper Contributor
Sep 13, 2021

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...
excel
Formulas and Functions
Macros and VBA
office 365
power bi
Yea_So's avatar
Yea_So
Bronze Contributor
Sep 18, 2021

Eastcoastbre 

 

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

Resources