Forum Discussion

wlknsnBI's avatar
wlknsnBI
Icon for Helper II rankHelper II
6 years ago
Solved

Power Query: Conditional Combine Columns

Hi,   I'm looking for a solution to dynamically combine a string based on a fix set of columns. In the table below I'm looking for the following result (Results column):   Table Fields Filte...
  • Vvelarde's avatar
    6 years ago

    hi, i recommend it create one column for each case using "Conditional Column"and after that "Merge Columns".

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NKylW0lECoVidaCXn/LySxGSwkFtmUXGJX2Juqo6CTyKEBVPlk5qYAtUVUlmQahuj5JdaHgM1IbE4FSQXXJqUlZpcAmKllqUWZZZU2npkpmcoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Table = _t, Fields = _t, Filter = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table", type text}, {"Fields", type text}, {"Filter", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Fields] <> "" or [Filter] <>"" then [Table] & "?" else ""),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Fields] <> "" then "$select=" & [Fields] else ""),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Filter] <> "" then "$filter=" & [Filter] else ""),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Fields] = "" and [Filter] = "" then [Table] else ""),
        #"Inserted Merged Column" = Table.AddColumn(#"Added Custom3", "Merged", each Text.Combine({[Custom.3], [Custom], [Custom.1], [Custom.2]}, ""), type text)
    in
        #"Inserted Merged Column"

     

    Regards

     

    Victor