Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |