Forum Discussion
Returning names on concatenated text
Hello,
I've been struggling to find the solution on this Power Query problem for awhile. Hopefully, someone might be able to help me.
As shown below, I have two queries; Rule List and Rule Performance.
-Rule List itemizes each rule number and displays the name of the rule.
-Rule Performance indicates which rule has fired.
What I'm trying to do is merge the queries together, by matching the concatenated numbers from Rule Performance to the Rule List, and return the names of each rule as a concatenated text.
Example: Second column on rule performance would read as
Names Fired row 1: A, B, C, D, E
Names Fired row 2: E, D, C
Names Fired row 3: B, E, A
Any ideas how I might go about this?
Hi Luke__, you can find similar code to Vijay_A_Verma here:
Vijay_A_Verma, it should be otherwise null because if you have otherwise "" then it will include that blank to Names Fired
Result
Just replace RuleTable code with your rule table refenrece and Source code with your table reference (where you have Rules Fired column)
let RuleTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hce3DQAwDAOwXzR7SC9j2hWG/38jhrhRFTHlUhsECyYKrns3yw3vYbnpvSwXvA9mHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rule Number" = _t, #"Rule Name" = _t]), ChangedType = Table.TransformColumnTypes(RuleTable,{{"Rule Number", Int64.Type}}), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE101EAU+Y6CmDKQkcBTFnqKIApA6VYnWglCBMhBVEKloLphimBmKoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rules Fired" = _t]), Ad_NamesFired = Table.AddColumn(Source, "Names Fired", each Text.Combine(List.Transform(Text.Split([Rules Fired], ","), (x)=> try ChangedType{[Rule Number = Number.From(Text.Trim(x))]}[Rule Name] otherwise ""), ", ") , type text) in Ad_NamesFired
2 Replies
Insert a custom column named Names Fired and put below formula in that
Text.Combine(List.Transform(Text.Split([Rules Fired], ","), (x)=> try #"Rule List"{[Rule Number = x]}[Rule Name] otherwise ""), ",")Hi Luke__, you can find similar code to Vijay_A_Verma here:
Vijay_A_Verma, it should be otherwise null because if you have otherwise "" then it will include that blank to Names Fired
Result
Just replace RuleTable code with your rule table refenrece and Source code with your table reference (where you have Rules Fired column)
let RuleTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hce3DQAwDAOwXzR7SC9j2hWG/38jhrhRFTHlUhsECyYKrns3yw3vYbnpvSwXvA9mHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rule Number" = _t, #"Rule Name" = _t]), ChangedType = Table.TransformColumnTypes(RuleTable,{{"Rule Number", Int64.Type}}), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE101EAU+Y6CmDKQkcBTFnqKIApA6VYnWglCBMhBVEKloLphimBmKoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rules Fired" = _t]), Ad_NamesFired = Table.AddColumn(Source, "Names Fired", each Text.Combine(List.Transform(Text.Split([Rules Fired], ","), (x)=> try ChangedType{[Rule Number = Number.From(Text.Trim(x))]}[Rule Name] otherwise ""), ", ") , type text) in Ad_NamesFired
