Forum Discussion

BeAStar's avatar
BeAStar
Frequent Visitor
4 years ago
Solved

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

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!!

Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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/

     

     

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    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's avatar
      BeAStar
      Frequent Visitor
      4 years ago

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