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

[BeAStar]

I have multiple text files that I have combined into one table, some of the files contain the same record and I want to combine the duplicated rows into one and  have a new column that shows the concatenation of one of the fields from the text files.

Example: 2 text files, File1 and File2; File1 has columns A (unique ID), B, D and E which has value "Conference X"; File2 file has A (unique ID), B, C and E which has value "Conference Y"; I need to combine the files and for rows in File1 and File2 where Column A has the same value I want to have one row (Column A) and columns B,C and D, but need a new column F that concatenates the values from Column E from the different files where column A is the same

Appreciate the help!! Thank you!!

[tackytechtom · Accepted solution]

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/

[tackytechtom · Accepted solution]

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/

[BeAStar · Accepted solution]

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