Combine two tables creating more rows and columns

[jinweitan]

Hi,

I am trying to combine two tables where:

Table 1:

ID	Question 1	Question 2
1	A	A
2	B	A

Table 2:

ID	Question 3	Question 4
3	B	C
4	C	A

And hoping to get a result of this:

ID	Question 1	Question 2	Question 3	Question 4
1	A	A
2	B	A
3			B	C
4			C	A

Wonder if I can get any advice on this.

Many thanks.

[amitchandak · Accepted solution]

@jinweitan , Append in power Query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Video -https://youtu.be/KyXIDInZMxk

[FrankAT]

Hi @jinweitan 

you can do it with Power Query like this:

// Table 1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE41idaCUjIMsJwosFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Question 1" = _t, #"Question 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 1", type text}, {"Question 2", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Table 2"})
in
    #"Appended Query"

// Table 2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUXICYmelWJ1oJRMwS0fJUSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Question 3" = _t, #"Question 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 3", type text}, {"Question 4", type text}})
in
    #"Changed Type"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)