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 tried merge by delimiter and split it, but I still get empty cells. 

Could anyone help with it?

 

Need Help
Tutorial Requests

3 Replies

  • 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