Combine rows from multiple sources into single row adding a column identifying source

Hi BeAStar ,

Here is my solution:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough) Note my tables have the prefix Table27_.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3NTUot8nRR0lEyNDQEkkZGRkDSOT8vLbUoNS85VSFCKVYHTZ0RWJ0xQXXGYHUmBNWZgNWZYqiLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column D" = _t, #"Column E" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", Int64.Type}, {"Column D", Int64.Type}, {"Column E", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column A", "Column B"}, Table27_File2, {"Column A", "Column B"}, "Table27_File2", JoinKind.FullOuter),
#"Expanded Table27_File2" = Table.ExpandTableColumn(#"Merged Queries", "Table27_File2", {"Column A", "Column B", "Column C", "Column E"}, {"Table27_File2.Column A", "Table27_File2.Column B", "Table27_File2.Column C", "Table27_File2.Column E"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Table27_File2",null,each [Table27_File2.Column A],Replacer.ReplaceValue,{"Column A"}),
#"Replaced Value 2" = Table.ReplaceValue(#"Replaced Value",null,each [Table27_File2.Column B],Replacer.ReplaceValue,{"Column B"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value 2", "Column F", each if [Column E] = null and [Table27_File2.Column E] <> null then [Table27_File2.Column E] else if [Column E] <> null and [Table27_File2.Column E] = null then [Column E] else [Column E] & ";" &[Table27_File2.Column E]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column E", "Table27_File2.Column A", "Table27_File2.Column B", "Table27_File2.Column E"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Table27_File2.Column C", "Column C"}})
in
#"Renamed Columns"

Hope this helps! 🙂 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

This is AMAZING!!!! Thanks Tom!!!!