Forum Discussion

tmul's avatar
tmul
Frequent Visitor
5 years ago
Solved

Conditional column merge

Hi, I have the following problem of merging columns. I have a dataset with around 70 columns. It is a result from a merge with another dataset and some columns contain the same data. I would like t...
Need Help
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Use Table.CombineColumns() to achieve your goal.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUVKK1YlWMgMyzMAsEyDLHMwCMiyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Master column A" = _t, #"Sub column A" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Master column A", Int64.Type}, {"Sub column A", Int64.Type}}),
    #"Combined Columns" = Table.CombineColumns(#"Changed Type", {"Master column A", "Sub column A"}, each if _{0} is null then _{1} else _{0}, "Combined column A")
in
    #"Combined Columns"
Payeras_BI's avatar
Payeras_BI
Solution Sage
5 years ago

Hi tmul ,

In PQ editor you could try something like this.

List.First(List.Select({[Master], [Column1], [Column2], [Column3],[Column4]}, each _ <> null))

 

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    5 years ago

    Sorry tmul - I misread your post and thought you needed a COALESCE type solution for ~70 columns. Below doesn't seem like what you are looking for. I mostly decided to 'try' to improve the formula when there were more than just a few columns (e.g. ~70).

    Payeras_BI - Try this instead of declaring all the column names:

    = Table.AddColumn(#"Changed Type", "Custom", each List.First(List.RemoveFirstN(Record.ToList(_), each _ = null),null))

    and also the opposite:

    = Table.AddColumn(#"Changed Type", "Custom", each List.Last(List.RemoveLastN(Record.ToList(_), each _ = null),null))