Power Query: Conditional Combine Columns

[wlknsnBI]

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	Filter	Result
Accounts			Accounts
Contacts	FirstName,LastName		Contacts?$select=FirstName,LastName
Leads		Type="New"	Leads?$filter=Type="New"
Cases	Subject	Severity="High"	Cases?$select=Subject&$filter=Severity="High"

So:

• if Fields OR Filter <> "": [Table] & "?"
• if Fields <> "": "$select=" & [Fields]
• if Filter <> "": "$filter=" & [Filter]
• if Fields AND Filter <> "": Add "&" between [Fields] & [Filter]

[Vvelarde · Accepted solution]

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

[amitchandak]

@wlknsnBI 

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output.

[wlknsnBI]

Above is table with 4 columns. I need to combine the 3 columns called [Table], [Fields] & [Filter]. The combination should come in the [Output] column but keeping in mind the rules I identified at the bottom. So have all the data & the output right there.