Forum Discussion
Helper IIPower 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 | 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]
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
3 Replies
- amitchandak
Super User
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.
wlknsnBIAbove 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.
- Vvelarde
Community Champion
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
