Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
In power query what process should I follow so if I introduce the first column with header ¨Sample¨ I would generate the second column with header ¨Result¨?
The text that will be my ¨Result¨ could be find beginning, middle or end of the text.
The ¨Result¨will be a concatenation of DQA, STP or QA, # and then 4 numerical digits.
Thank you in advance for your help.
Solved! Go to Solution.
This is very helpful. I don't have much experience with Power Query.
Thank you very much guys.
Hi @josectps,
for future requests provide sample data as table so we can copy/paste.
Try this:
If you want to check every single step, remove this part of code and expand the record.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY2xCoMwGIRf5YhrK7/GWjqGuhQcKpUu4pDWoKGaSBL6/A2CQ+GWO7676zp2naQZFYJFfqZlgV/lW5sRVSOOGSfWHzr2aO+cMjyVC/otZ7ykw2ztx2O0dtiQRiREJw5R12ma7q2EMp7jFqA9jA1b3E7RRMWDJCeCNj4oOeyVoij/jjZWG8xflBuTXQiNQKVmFRR8cHrF4GScYX3/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sample = _t]),
Ad_Result = Table.AddColumn(Source, "Result", each
//remove characters
[ a = Text.Remove([Sample], {"#", "-"}),
//text to list by space delimiter
b = Text.Split(a, " "),
//extract strings containing "DQA", "STP" and "QA"
c = List.Accumulate({ "DQA", "STP", "QA" }, {}, (s,c)=> s & List.Select(b, each Text.Contains(_, c ) ) ){0}?,
//combine string with number (reorder text first, number afterwards)
d = if List.ContainsAny(Text.ToList(c), {"0".."9"}) then c
else [ d1 = List.PositionOfAny(b, { "DQA", "STP", "QA" }),
d2 = if List.Contains(Text.ToList(b{d1+1}?),{"0".."9"}) then c & b{d1+1}?
else c & b{d1-1}?
][d2],
//split text and numbers separately to list
e = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"0".."9"}, x), {"0".."9"})(d),
//combine text with # and add 0 as prefix if necessary to match 4 character length number
f = e{0} & "#" & Text.PadStart(e{1}, 4, "0")
][f], type text)
in
Ad_Result
This is very helpful. I don't have much experience with Power Query.
Thank you very much guys.
Hi @josectps
This might work
You need to change some symbols etc first in the existing column.
Text.Split([Sample], " ")
This should help.
Thanks
Joe