Forum Discussion
Merging multiple tables in power query
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])
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?
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.
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.
