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.
I have a requirement to extract/split data if value starts with P or QM to be 7 characters rest should be as-is, how it can be achived? help needed
As-is
PABCP01A
CSOMTO04
PPQRX01M
QM0888TA
ATLCTL01
to-be
PABCP01
CSOMTO04
PPQRX01
QM0888T
ATLCTL01
Solved! Go to Solution.
@Anonymous -
Are you meaning that you have so many conditions for Text.StartsWith = "P" || "QM" || etc.?
If so, then you should include those requirements in your question.
Otherwise, you just need to add the logic from the last statment
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") then Text.RemoveRange([#"As-is"],7) else if Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"])
Proud to be a Super User!
@Anonymous -
This seems to work:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnB0cg4wMHRUitWJVnIO9vcN8TcwAXMCAgKDIgwMfcGcQF8DCwuLEIgyxxAf5xAfA0Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"As-is" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"As-is", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") then Text.RemoveRange([#"As-is"],7) else if Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"]) in #"Added Custom"
Proud to be a Super User!
Hi ajitk15,
You could follow above ChrisMendoza's suggestions or simplify code by "Or " condition like below
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnB0cg4wMHRUitWJVnIO9vcN8TcwAXMCAgKDIgwMfcGcQF8DCwuLEIgyxxAf5xAfA0Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"As-is" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"As-is", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") or Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"]) in #"Added Custom"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous -
Are you meaning that you have so many conditions for Text.StartsWith = "P" || "QM" || etc.?
If so, then you should include those requirements in your question.
Otherwise, you just need to add the logic from the last statment
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") then Text.RemoveRange([#"As-is"],7) else if Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"])
Proud to be a Super User!
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |