Merging multiple tables in power query

[marksman1941]

Hi all. Brand new user here, but I've been lurking for a few weeks. I'm new to Power BI, and while I think I'm getting close to a solution I still don't have things quite right regarding my use of Power Query. 

I will be getting monthly spreadsheets with fairly messy data on them that will need cleaned up on a regular basis. The spreadsheet I am working with currently has 5 separate tables with several common column titles, and several unique to each spreadsheet. The data contained is personnel information, and some names are duplicated across multiple tables. My goal is to have a single iteration of each name whether it appears 1 or 5 times, and all information from each table for that person drawn into a single table. The data set is much larger, but the table below is representative of the raw input I have. 

Record ID	Last Name	First Name	Gender
111	Smith	James	M
222	Johnson	Jack	M
333	Random	Some	F
Record ID	Last Name	First Name	Gender	Status?
111	Smith	James	M	Active
222	Johnson	Jack	M	Inactive
333	Random	Some	F
Record ID	Last Name	First Name	Gender	Code	Location
111	Smith	James	M	6	Downtown
222	Johnson	Jack	M	5	Home
333	Random	Some	F	6	Local
444	Subject	Test	F	2
Record ID	Last Name	First Name	Gender	Code	Location
111	Smith	James	M	6	Downtown
222	Johnson	Jack	M	5	Home
333	Random	Some	F	6	Local
444	Subject	Test	F	2

My goal for the data outcome is to look like this

Record ID	Last Name	First Name	Gender	Status?	Code	Location
111	Smith	James	M	Active	6	Downtown
222	Johnson	Jack	M	Inactive	5	Home
333	Random	Some	F		6	Local
444	Subject	Test	F	Inactive	2

My process so far has been -

* Ensure all headers are identical

* Get Data from Table and save as a connection, for all 5 tables. 

* Merge Queries, using the Record ID, Last Name, First Name, and Gender as join kind, left outer. 

* Expand Table 2, uncheck Record ID, Last Name, First Name, and Gender, click OK

When I do this method I get all of my information in one spreadsheet and things seem functional. I wreck on merge2. 

When I import in a table that has new names or report ID's, they aren't added to the spreadsheet at all. I'm limited to the initial group of people who were in the initial Table 1.

To rectify this, I copied every single person into a single list with Report ID, Last Name, First Name, and used that as the start of my table that I merged everything with. Merge1 looks fine and works great, using the above 3 headers as join kind. When I try to bring in a third table however, no combination (that I have found) of join kind columns or checked/unchecked columns in the power query editor will allow all of the personnel data from table 3 to move over. 

I am very clearly using this improperly, but I feel like I'm just missing a step or two somewhere. Is anyone able to help identify where I'm going wrong? Thank you in advance!

[AlienSx]

Hi, @marksman1941 combine your tables with Table.Combine, then group by ID and apply some custom function that fills up and down all the columns (I don't know what columns except Record ID always has data so it might be an overkill). Please note that in order Table.FillUp/Down to work you must have nulls in empty "cells" of your tables. 

    a = Table.Combine({t1, t2, t3, t4, t5}),
    // this little custom function replaces blanks with nulls,
    // fills up/down all columns of the table and returns single record
    f = (t as  table) as record => 
        let 
            cols = List.Buffer(Table.ColumnNames(t)),
            replace_blanks = Table.ReplaceValue(t, "", null, Replacer.ReplaceValue, cols),
            up = Table.FillUp(replace_blanks, cols),
            down = Table.First(Table.FillDown(up, cols))
        in down,
    // group by Record ID, apply function 
    groups = Table.Group(a, "Record ID", {{"all", each f(_)}}),
    out = Table.FromRecords(groups[all])

[marksman1941]

As silly as this may sound, it seems like the Append function did exactly what I was looking for. Are there any unintended side effects with using Append?

[AlienSx]

Duplicated Record IDs as opposed to what you show as you "goal for the data outcome". BTW there is no "Append function" in M language. It's a UI's name which effectively resembles to Table.Combine function behind the scene that I use in the very first row of code. 

[marksman1941]

I appreciate the help! I'm still using fairly surface level operations here, as I haven't used power.bi much before now. I'm glad that the built in append function works similar to what you were referring to. 

When I ran the append function, it did give me about 20 duplicates of the Record ID lines. I ran a remove duplicates tool which pulled those and left me with what looked to be a clean data set, although i haven't verified that all 600 lines transferred properly.