Add column from query to another without appending

user_13562186 0
Viewed 92

I have 2 tables which exist as queries in power query. The tables have the same number of rows (even if empty) and I simply wish to join rows from the second query to the end of the first however this is proving more difficult that it should. Below is a simplified example of what I want to achieve.

enter image description here

I don't wish to merge the queries since neither of the columns share values. Alsovthe append feature adds the second query to the right but underneath. Similarly if I say add custom column = to the query name. This loads a table but when I expand this the values are duplicated for each values of the first query.

Im sure this is a very easy question. Perhaps too easy because I cant seem to find much online.

Update: I can get it to work with indexing but I wonder if there is a simpler way

powerquery excel
1 Answers

Very peculiar use case, but to join this you would need to add index columns to both tables and do a regular inner join merge on the index columns. I hope you have complete control over the value ordering in your query when you have no common columns to merge tables on!

Table 1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgy1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Table 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjNyclX0lFyLCjISVWK1QGK5OckAgWcEvOAsBgilAhR5JyRWlRUqRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Merged:

let
    Source = Table.NestedJoin(Table1, {"Index"}, Table2, {"Index"}, "Table2", JoinKind.Inner),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column1", "Column2"}, {"Table2.Column1", "Table2.Column2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table2",{"Column1", "Column2", "Table2.Column1", "Table2.Column2"})
in
    #"Removed Other Columns"
user_165280000
Related