Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I pull via DirectQuery a column called 'Comments'. Some rows of this column contain text with different length and symbols. Do you know any formula which I can use for the below formats in order to extract only the comment?
Comments (current view) | The desired result |
. exciting. | . exciting. |
++++ | ++++ |
08.00h. NNP | NNP |
020 John Will O'Keefe: 00:30 (2019.03.01) Working O.E. | Working O.E. |
021 Andrew Olsen: 00:45 (2020.04.05) Not Working | 00:45 (2020.04.05) Not Working |
The most needed transformation is for the 4th and 5th rows- In the 4th row I need only the text after the date, and in the 5th row I need everyting after the column sign (:)
Thank you in advance.
Hi @EmaVasileva
Try the below or download the attached pbix file for details
Measure =
VAR __comments = SELECTEDVALUE( 'Table'[Comments (current view)] )
VAR __len = LEN( __comments )
VAR __notWorking = SEARCH( "Not Working", __comments, 1, 0 ) > 0
VAR __working = SEARCH( "Working O.E.", __comments, 1, 0 ) > 0
VAR __NNP = SEARCH( "NNP", __comments, 1, 0 ) > 0
RETURN
SWITCH(
TRUE(),
__notWorking, MID( __comments, SEARCH( ":", __comments, 1, 0 ) + 1, __len ),
__working, "Working O.E.",
__NNP, "NNP",
__comments
)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY9BC4JAEIX/ysNTkgzPVam8dehSoN08mKfaUpIVzKif3+5CQXToXYb3ZuYbpq4DgX4eu6kzF4FTEP1ENmyiOphb2a4vznMpZCsoir3b8sXnitgOrUHV9T3Kw51Ui53WZ52DzBNiphivhIkwDlEN49WeQikbcZwvjzcyxtqcRv1A2d+08aA0cyBFYSrMQhTD9IH5P/7PNM0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Comments (current view)" = _t, #"The desired result" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Comments (current view)", type text}, {"The desired result", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([#"Comments (current view)"], "NNP ")
then "NNP"
else if Text.Contains([#"Comments (current view)"], "Working O.E.")
then "Working O.E."
else if Text.Contains([#"Comments (current view)"], "Not Working")
then Text.Trim(
Text.Middle(
[#"Comments (current view)"],
Text.PositionOf( [#"Comments (current view)"], ":" ) + 1
)
)
else [#"Comments (current view)"])
in
#"Added Conditional Column"
As you are working with Direct Query both might not work, so pushing this calculation to data source might be an option.
Hi @Mariusz
Apologies for my delayed response and thank you for the help. None of the solutions work in DirectQuery mode and as a final workaround I imported the needed table and I used the second formula.
I will accept your post as a Solution. Thanks.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |