Merge Sheets Power Query

Hello All,

I am trying to merge 4 sheets into one using common columns for each unique row.

See attached.

These queries are retrieving data from a files on my HD. I added columns "Count" and " Unique BN" to give each row a unique value to avoid duplicates.

I have highlighted the following columns as common columns:

Total Amount
Date Requested

My hopes was I could use the power query append to slam these together and put them in all of the unique rows into one sheet.

Personally, I'd do all transformation in PowerQuery and skip loading tables into sheets. If you can upload sample source files. I can show you steps.

If that's not feasible. You'd load each table as connection only to PowerQuery.

Remove all unnecessary columns from each table and make sure all remaining column names match from table to table.

Then using one of the query, "Append Queries"->three or more.

Highlight all columns and "Remove Rows"->"Remove Duplicates".

Change column data type as needed and load to sheet (or data model).

See attached sample.

Note: I didn't filter out "null" values, but you may want to.

Originally Posted by CK76

Personally, I'd do all transformation in PowerQuery and skip loading tables into sheets. If you can upload sample source files. I can show you steps.

If that's not feasible. You'd load each table as connection only to PowerQuery.

Remove all unnecessary columns from each table and make sure all remaining column names match from table to table.

Then using one of the query, "Append Queries"->three or more.

Highlight all columns and "Remove Rows"->"Remove Duplicates".

Change column data type as needed and load to sheet (or data model).

See attached sample.

Note: I didn't filter out "null" values, but you may want to.

Sweet, you did it. Question. My source files have duplicate Site ID's across the 4 different sheets so I used the query to create a unique site id (UBN). Do you still want the source files?

How do I load each table as connection only to powerquery?

How do I remove columns? Query Editor? When I go into Query Editor, my UBN number and count columns isn't listed.

Do you still want the source files?

Only if you need help merging without loading individual tables into sheet.

How do I load each table as connection only to powerquery?

Once connection is made, go into editor. Do necessary transformation and click on "Close & Load To". In the menu choose "Only Create Connection".
Once all transformation and merge etc are done. You can load end result to a sheet or data model.

How do I remove columns? Query Editor?

Yes. If you are looking at my sample. You can see where I removed columns in "Applied Steps" pane at the right hand side of editor window.

Originally Posted by CK76

Only if you need help merging without loading individual tables into sheet.



Once connection is made, go into editor. Do necessary transformation and click on "Close & Load To". In the menu choose "Only Create Connection".
Once all transformation and merge etc are done. You can load end result to a sheet or data model.


Yes. If you are looking at my sample. You can see where I removed columns in "Applied Steps" pane at the right hand side of editor window.

Ok . . the issue I've got now is before I do the append, I add two columns (Count and Unique B N) so I can give each row a unique identifier. When I slam them into the Append, they get deleted or disappear. How do I preserve these?

You can add Count and Unique BN within PowerQuery.

How?

For the count column, i put "1" in row 2, and "2" in row 3 and drag it down so it continues the count.

For the Unique BN, I am concatenating columns.

For Count just add custom column -> Add Index Column -> From 1

For Unique BN. Use concatenation within PowerQuery.

You can either do it via formula. Or just select both columns to concatenate and right click, choose "Merge Columns".
This will create new column that will replace original columns.