Forum Discussion
wlknsnBI
Helper II
6 years agoPower 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...
- 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
amitchandak
Super User
6 years agoThe 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.
- wlknsnBI6 years ago
Helper II
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.