let
step_01a = Excel.Workbook(File.Contents(path_a), null, true),
step_01b = Excel.Workbook(File.Contents(path_b), null, true),
step_02a = step_01a{[Item="Data", Kind="Sheet"]}[Data],
step_02b = step_01b{[Item="Data", Kind="Sheet"]}[Data],
step_03a = Table.PromoteHeaders(step_02a, [PromoteAllScalars=true]),
step_03b = Table.PromoteHeaders(step_02b, [PromoteAllScalars=true]),
step_04a = Table.TransformColumnTypes(step_03a, {
{'KEY_A', type text},
{'VALUE_A', type number}
}),
step_04b = Table.TransformColumnTypes(step_03b, {
{'KEY_B', type text},
{'VALUE_B', type number}
}),
step_05 = Table.Join(
step_04a, "KEY_A",
step_04b, "KEY_B",
JoinKind.LeftOuter
)
in step_05
In this M Script I have brought in two Excel files (step 1), extracted the data (step 2), marked the first row as headers (step 3), set the column data types (step 4), and joined the tables using KEY_A and KEY_B. The output will be a table that has KEY_A, VALUE_A and VALUE_B.