Two tables, identical columns and rows, some blanks in cells, need to combine data

DirectorAlwyn

New member
Joined
Apr 4, 2016
Messages
8
Reaction score
0
Points
0
Hello all,

The scenario here is that I have two tables with the same rows and columns (not originally, but after doing some Power Query cleaning). The problem is that in one table, some of the fields will have data while others have nothing, while in the other table different fields might have data while others are missing. What I'm currently doing is to check of each set of column pairs and do an "if [tableAcolumn1] = null then [tableBcolumn1] else [tableAcolumn1]", then get rid of the original columns and just keep the formula results columns, rename and done.

It feels like this is a very clunky way of doing this, though, and while this particular report has only a dozen columns, there are other situations I'd like to be able to do this for that might have many more columns. Does anyone know of a better way to do this? Honestly at this point I'm considering coming up with some Excel formulas to take a list of the column names and do some formulas to write the PQ code for me, which seems crazy convoluted.

Merging adds additional columns, and appending adds additional rows, and is mostly what I've done before when checking if one side or another had data, but in this particular case either side could have some ​of the data, and I need to mash both together. I feel like there is a way to do this simply that I'm just not realizing, so any help would be welcome.
 
hi!!

What you're doing sounds good tbh. On top of my mind, i think that most straightforward way of solving this scenario would be by doing a Merge between those 2 tables and then down the needed comparisons. Sadly, Power Query doesn't have a feature to compare tables. I have not tested this, but the following article might help you http://www.excelandpowerbi.com/?p=237
 
I was afraid of that. Well, hopefully in the future they'll add some table comparison logic, I have to imagine that's a pretty big use case and the logic is relatively straightforward if you assign the matching columns.
 
Back
Top