Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
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 |
Hello, @SaltaSultan
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.