Forum Discussion
Combining and merge two rows in one
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 [email protected] Brazil - -
Table 2 Jhon [email protected] - 25 Blue
But, i wuold like to have this:
Name Email Country Age Color
Jhon [email protected] 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/td-p/29466
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!
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.
2 Replies
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"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.
