how to combine these two csv files with unmatched columns and the same row count ?

[OmarSalahelDine]

 

[v-xinruzhu-msft · Accepted solution]

Hi @OmarSalahelDine 

You can create two blank queries, then put the following codes to Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtYzsVTSUXLMSUrMy0w0tLQ0AvIM9MwMTZVidQhKm+mZGOLVbWiMR9pIz8KUfLsH0mnD0+WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"sucides/100k pop" = _t, #"country-year" = _t, #"HDI " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sucides/100k pop", type number}, {"country-year", type text}, {"HDI ", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJSszLTFTSUTK0tDQCUrmJOalAythU19REoTI1sagYJGekFKuDW7Ghqa4RQrElXrWmprrmCLWmeNWiOsKcBDfgV4vqBhMS3DAaEEM4IGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, year = _t, sex = _t, age = _t, suncides_no = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"year", Int64.Type}, {"sex", type text}, {"age", type text}, {"suncides_no", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, Query1, {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"sucides/100k pop", "country-year", "HDI "}, {"sucides/100k pop", "country-year", "HDI "})
in
    #"Expanded Query1"

Output

Best Regards!

Yolo Zhu

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