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 All,
I've search multiple responses in this forum and it seems like they can't solve the result I wanted (hopefully I've searched enough, sorry about that if this is already answered)
I have multiple columns with phrases which are updates made by different teams so there would be rows with nulls and blanks on them. I was wondering if there would be a formula or power query way to merge the columns wherein they can merge these phrases with line breaks as well as starting each phrases that are not null with "-"
Sample table would be below:
Team A | Team B | Team C | Result Column |
null | null | null | |
null | |||
Company B will join the team meeting | All employees will agree on clocking in and out | -Company A will join the Company -All employees will agree on clocking in and out | |
Company A will join the Company this June | Every employee is on Leave | null | -Company A will join the Company this June -Every employee is on Leave |
null | null | null |
Solved! Go to Solution.
Hi, @Burubear
Here is the table. The pbix file is attached in the end.
Table:
You may go to 'Add Coumn' ribbon, click 'Custom Column', paste the following codes in the 'Custom column formula'.
=let
teama =
if [TeamA]="" then "" else "-"&[TeamA],
teamb =
if [TeamB]="" then "" else "-"&[TeamB],
teamc =
if [TeamC]="" then "" else "-"&[TeamC],
list = List.Select({teama,teamb,teamc},each _<>"")
in
Text.Combine(list,"#(lf)")
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Burubear
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Here are the codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BCoNADEWv8nHtJbR0U3oDcTHYj06dSURHy9y+YYpdCFmE/JeXdF1V1aX6+treNC5OMlp8fAh4qxekiUh0EZFMXkbDGssYl6CZ3H6oG1cSKhiCDrNhsFUnL+ieiv1UNxf1OU+T3/DYhea/H1zz/wIsMPGT7mD5tf8C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TeamA = _t, TeamB = _t, TeamC = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TeamA", type text}, {"TeamB", type text}, {"TeamC", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
let
teama =
if [TeamA]="" then "" else "-"&[TeamA],
teamb =
if [TeamB]="" then "" else "-"&[TeamB],
teamc =
if [TeamC]="" then "" else "-"&[TeamC],
list = List.Select({teama,teamb,teamc},each _<>"")
in
Text.Combine(list,"#(lf)")
)
in
#"Added Custom"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sorry not that advance yet in codings and using advanced query editor, would you show which formula used/ buttons clicked in each step
Hi, @Burubear
Here is the table. The pbix file is attached in the end.
Table:
You may go to 'Add Coumn' ribbon, click 'Custom Column', paste the following codes in the 'Custom column formula'.
=let
teama =
if [TeamA]="" then "" else "-"&[TeamA],
teamb =
if [TeamB]="" then "" else "-"&[TeamB],
teamc =
if [TeamC]="" then "" else "-"&[TeamC],
list = List.Select({teama,teamb,teamc},each _<>"")
in
Text.Combine(list,"#(lf)")
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you so much! it's now easier to understand for new PBI users like me. Works really well and got the result that I wanted!
You could use a custom function:
fnMergeComments = (l as list) as nullable text =>
let
FixList = List.Accumulate( l, "", (s, c) => if (c??"") = "" then s else s & "#(lf)- " & c ),
FixStart = if FixList = "" then null else Text.AfterDelimiter(FixList, "#(lf)")
in
FixStart,
#"Merge Comments" = Table.CombineColumns(PreviousStep ,{"Team A", "Team B", "Team C"},fnMergeComments,"Comments")
in
#"Merge Comments"
where PreviousStep is your last query step.
Best,
Spyros
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.