Combining and merge two rows in one

[marketingVE]

Hi everyone!

I would to like to know if is possible to merge two rows of the different tables in the one row in power query,  for exemple:

I have this:

Origin       Name     Email                                Country     Age    Color

Table 1     Jhon       Jhon@outlook.com          Brazil            -          -

Table 2     Jhon       Jhon@outlook.com               -             25     Blue

But, i wuold like to have this:

 Name     Email                                Country     Age    Color

 Jhon       Jhon@outlook.com          Brazil          25     Blue

I'm just don't know how i do it!

My problem is similar with this : https://community.powerbi.com/t5/Desktop/Combining-rows-based-on-unique-id-and-combining-information...

But i don't understand this post very well. So please, help me!

PS: Sorry for my english, i'am just a brazilian young guy! 

[Icey · Accepted solution]

Hi @marketingVE ,

You can also try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklMyklVMFTSUfLKyM+DUg75pSU5+fnZesn5uUAhp6LEqswcIAOEYnVgmozwagIiI1OQ5pzSVCRNIJsccyshJPH24NICssUEbkssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Origin = _t, Name = _t, Email = _t, Country = _t, Age = _t, Color = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Origin", type text}, {"Name", type text}, {"Email", type text}, {"Country", type text}, {"Age", Int64.Type}, {"Color", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Email", "Origin"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Origin"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

all steps are performed via the GUI

let
    rec1=[f1="a",f2=null,f3=1],
    rec2=[f1="a",f2="b",f3=null],
    tfr=Table.FromRecords({rec1,rec2}),
    #"Intestazioni abbassate di livello" = Table.DemoteHeaders(tfr),
    #"Trasposta colonna" = Table.Transpose(#"Intestazioni abbassate di livello"),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Trasposta colonna", "merge", each [Column2]??[Column3]),
    #"Rimosse altre colonne" = Table.SelectColumns(#"Aggiunta colonna personalizzata",{"Column1", "merge"}),
    #"Trasposta colonna1" = Table.Transpose(#"Rimosse altre colonne"),
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"Trasposta colonna1", [PromoteAllScalars=true])
in
    #"Intestazioni alzate di livello"