Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have columns C-Z that contain various statements. I'm looking for the formula that will look at columns C-Z and for each of the columns that contain one of the following words ("Highly", "Likely", "Unfamiliar"), I would like to return the full value/statement of that cell into the new cell.
Example:
Column C: Johnny is highly capable of the job.
Column D Sarah is likely to join.
Column E: Sally is unfamiliar to the project.
Column F: Tommy is having a great day.
End result needed for the new column:
Johnny is highly capable of the job.
Sarah is likely to join.
Sally is unfamiliar to the project.
I've tried doing a conditional column but it only returns the first result found within those columns instead of all three results.
I've tried doing an if text.contains for the specific columns containing "highly" and used OR to separate the various Text.Contains for the various columns and tell it to return the column.
Example: (if Text.Contains([Column C], "highly") or Text.Contains([Column D, "highly") then [Column C][Column D] else null.
(I've tried with "&" and "or" between the 'then' columns and still get an error.)
In doing so, I get the following error:
Expression.Error: We cannot convert the value "Finding" to type Logical.
Details:
Value=Finding
Type=[Type]
The column type for columns C-Z is text, as well as the source column. The custom column type is coming in as alphanumeric. I've changed the type of the custom column to text but that's not eliminating the error.
I think you want something like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY2xDoMwDAV/xcpc8SFd2w0xPKhJTB0bpWml/D2EznenG8dw92TWSD6UJCZttGDHrEy+Uk1Mm8/hFh4oSF1SefMpVT+B2HAh1av/2oosKigd93YvvvFSu/X0nP8X/MQigWJhVHqhDWGaDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C = _t, D = _t, E = _t, F = _t]),
ColsToSearch = {"C", "D", "E", "F"},
Keywords = {"highly", "likely", "unfamiliar"},
#"Added Custom" = Table.AddColumn(Source, "Custom", each
Text.Combine(
List.Select(
Record.ToList(Record.SelectFields(_, ColsToSearch)),
(txt) => List.ContainsAny(Text.Split(txt, " "), Keywords)
)
, "#(cr)"
)
, type text
)
in
#"Added Custom"
Custom column explanation provided by GPT4:
For each row in the original table, it does the following:
ColsToSearch
using Record.SelectFields(...)
.Record.ToList(...)
.Text.Split(txt, " ")
) and then checking if any of those split parts match the keywords using List.ContainsAny(...)
.List.Select(...)
.Text.Combine(...)
, with each value separated by a carriage return (#(cr)
).Thank you for your quick response. I was using dummy data in my question so when I copy/paste that into my query it's returning it as noted in your screenshot, however, since it was dummy data I'm having a hard time converting it to match my query.
Is is possible to get the exact steps taken to get the resolution or perhaps a link to a good video that would explain these steps? While the bulleted items in your response make sense to me, unfortunately, I'm not familiar with Json.Document and the first part of the formula.
IE: My true column headers range from AC Findings.1 through AC Findings.20. Random columns may contain a blank on randow rows while others contain various data/values in which I would want to look for "Highly" "Significant" & "Critical".
The first line starting with Source is where I've defined the dummy data using the Enter data button in Power BI (it saves the table data in a compressed JSON format). This step will be replaced by whatever your actual source is or all the steps in your query so far.
Your query might look something like this:
let
Source = [...],
[...],
#"Last Query Step" = [...],
ColsToSearch = List.Transform({1..20}, each "AC Findings." & Number.ToText(_)),
Keywords = {"highly", "likely", "unfamiliar"},
#"Added Custom" = Table.AddColumn(#"Last Query Step", "Custom", each
Text.Combine(
List.Select(
Record.ToList(Record.SelectFields(_, ColsToSearch)),
(txt) => List.ContainsAny(Text.Split(txt, " "), Keywords)
)
, "#(cr)"
)
, type text
)
in
#"Added Custom"
where the part before ColsToSearch is whatever you already have. Be sure to reference the last step inside Table.AddColumn(<here>, ...).