How to merge two columns with slight differences

[Anonymous]

Hi! 

I've got two columns that reflecting exact data, but the problem is that some data missed in first columns contains the second column. How can I merge data only to fill the empty cells? 

I've tried merge by delimiter and split it, but I still get empty cells. 

Could anyone help with it?

[jennratten]

Can you please add a sample of the data your are merging on?  In general, when a cell is empty following a merge expansion it's because a match was not found for its key.  Without seeing what happens before the results it is hard to provide more guidance.

[Anonymous]

So here is two criteria columns like "Personnel ID" and "Course Code" they unique. By them I'm getting the columns "Session Date" and "Status" columns from one report and "Session Date.1" and "Status.1" columns data from another report. So, I've got in hands table with similar data in several columns. And I'd like to merge them into one, but only into those cells where data missed, but exact data covered in another column. 

Personnel ID	Course_code	Session Date	Status	Session Date.1	Status.1
123456	LPG-LRNG1	6/2/2021	Ok	6/2/2021	Ok
123456	LPG-LRNG2	2/3/2023	Ok	2/3/2023	Ok
123456	LPG-LRNG3			9/20/2022	Ok
123456	LPG-LRNG4	4/15/2021	Ok	4/15/2021	Ok
123456	LPG-LRNG5			11/3/2021	Ok
123456	LPG-LRNG6			5/26/2011	Ok
123456	LPG-LRNG7			12/11/2013	Ok
123456	LPG-LRNG8	4/6/1993	Ok
123456	LPG-LRNG9	4/6/1993	Ok

[AlienSx]

Hello, @Anonymous 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBLDoAgDESvYlhrastHOQEbo8Yt4QTef2/xF1RkQTKFN9NS7wWSVNqIWgyza4ZldMjaAAG1FOW0fupQZ2zEmkBGTF62Z521xbfqPJbhyFOBV6wVoH6M97rIGnXSCPEYrMSbhOdw3kCLJb5L8wm4BRtKP+/3wQ2gtffCjoAsbv/xsAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personnel ID" = _t, Course_code = _t, #"Session Date" = _t, Status = _t, #"Session Date.1" = _t, Status.1 = _t]),
    fx_merge = (x) => if x{0} = " " then x{1} else x{0},
    status = Table.CombineColumns(Source,{"Status", "Status.1"},fx_merge, "Status"),
    s_date = Table.CombineColumns(status,{"Session Date", "Session Date.1"}, fx_merge, "Session Date")
in
    s_date