Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to merge two columns with slight differences

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 ...
Need Help
Tutorial Requests
jennratten's avatar
jennratten
Super User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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 IDCourse_codeSession DateStatusSession Date.1Status.1
123456LPG-LRNG16/2/2021Ok6/2/2021Ok
123456LPG-LRNG22/3/2023Ok2/3/2023Ok
123456LPG-LRNG3  9/20/2022Ok
123456LPG-LRNG44/15/2021Ok4/15/2021Ok
123456LPG-LRNG5  11/3/2021Ok
123456LPG-LRNG6  5/26/2011Ok
123456LPG-LRNG7  12/11/2013Ok
123456LPG-LRNG84/6/1993Ok  
123456LPG-LRNG94/6/1993Ok  
  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    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