Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I have a column that contains a number (a year), which is NOT FIXED - it could be any year.
I need to replace it with the values of another column:
In this case, what in the column [First_Part] reads 2023 should be replaced with the corresponding CalculatedYear - and the additional text should be kept.
E.g., Jan Forecast Spend(2023 should be transformed into Jan Forecast Spend (2025
And again it should all be flexible - I cannot use 2023 as a reference... it's replacing the only number that will be in that column.
Can you please help me?
Thanks!
Kind regards
Valeria
Solved! Go to Solution.
Hi Valeria,
Paste this into a new blank query using Advanced Editor to see the steps to take:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSAVEmSrE60UpeiXkKbs4KwQWpeSkKGuiyvolFCm7JSLImEFkLsKxLajKqrClY1thUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [First_Part = _t, CalculatedYear = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"First_Part", type text}, {"CalculatedYear", Int64.Type}}),
splitColByDelim = Table.SplitColumn(chgTypes, "First_Part", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, true), {"First_Part.1", "First_Part.2"}),
addV2 = Table.AddColumn(splitColByDelim, "First_Part_v2", each if try Value.Is(Number.From([First_Part.1]), type number) otherwise false = true then Text.From([CalculatedYear])
else [First_Part.1] & " " & Text.From([CalculatedYear]))
in
addV2
I basically just split the original column by delimiter ( " (" ) then did a check to see if the original column value was numerical. If yes, then use [CalculatedYear], if no, then concatenate the split original with [CalculatedYear].
Example output:
Pete
Proud to be a Datanaut!
Hi, @ValeriaBreve suppose that this is the only "number" in the string. Then
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU3DLL0pNTiwuUTAyMDLWUNJRAtImSrE60SCGMZwfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [First_Part = _t, CalculatedYear = _t]),
numbers = List.Buffer({"0".."9"}),
r = Table.ToRecords(Source),
txf_field =
List.Transform(
r,
(x) =>
Record.TransformFields(
x,
{"First_Part", (y) => [s = Text.Select( y, numbers), u = Text.Replace(y, s, x[CalculatedYear])][u]}
)
),
result = Table.FromRecords(txf_field)
in
result
@AlienSx Hi, this works as well! However, I have a hard time to follow the steps. I did learn the function Record. TransformFields that I did not know existed! But I am not good enough for now to follow through the function in function step. I will get there in time ;-).
Thanks!
Kind regards
Valeria
Hello, @ValeriaBreve M functions handbook.
[s = Text.Select( y, numbers), u = Text.Replace(y, s, x[CalculatedYear])][u]
// is just a replacement to
let
s = Text.Select( y, numbers),
u = Text.Replace(y, s, x[CalculatedYear])
in
u
You may think of a query as a record with step_names = record field names. You may use "let ... in" instead - nothing wrong with that.
Text.Select (y, numbers) selects only chars "0".."9" from your string. Result is a concatenation of all "numerical" chars in your string. Next step replaces this substring with a text value from your CalculatedYear column.
p.s. I've just noticed that CalculatedYear is Int64.Type. So please wrap x[CalculatedYear] in Text.From.
@AlienSx thank you so much for taking the time to explain!!!!!! It is much clearer now. 🙂
Hi Valeria,
Paste this into a new blank query using Advanced Editor to see the steps to take:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSAVEmSrE60UpeiXkKbs4KwQWpeSkKGuiyvolFCm7JSLImEFkLsKxLajKqrClY1thUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [First_Part = _t, CalculatedYear = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"First_Part", type text}, {"CalculatedYear", Int64.Type}}),
splitColByDelim = Table.SplitColumn(chgTypes, "First_Part", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, true), {"First_Part.1", "First_Part.2"}),
addV2 = Table.AddColumn(splitColByDelim, "First_Part_v2", each if try Value.Is(Number.From([First_Part.1]), type number) otherwise false = true then Text.From([CalculatedYear])
else [First_Part.1] & " " & Text.From([CalculatedYear]))
in
addV2
I basically just split the original column by delimiter ( " (" ) then did a check to see if the original column value was numerical. If yes, then use [CalculatedYear], if no, then concatenate the split original with [CalculatedYear].
Example output:
Pete
Proud to be a Datanaut!
@BA_Pete Hi Pete, thanks! This works very well! I was trying to understand the code and there is something I cannot grasp.
When you write:
each try Value.Is(Number.From([First_Part.1]), type number) otherwise false = true
I do see it works, it converts the record from the "try" expression into the right "true/false" string without expanding any columns - but could you please elaborate into more details? I cannot figure it out.
Thanks!!!
Kind regards
Valeria
Haha, yes, when you highlight it like that it does look a bit odd "otherwise false = true" 🙂
It actually breaks down like this:
Number.From( ... ) tries to convert your value to a number. It outputs an error if it's not.
Value.Is( ... , type number) tries to turn the result of the above into a TRUE/FALSE output to satisfy the IF...THEN structure. The problem with this is that if your value isn't a number, it evaluates to an error due to the error thrown by our Number.From step. So =>
try ... otherwise false handles this error by saying "TRY to get a non-error value from the above OTHERWISE give a FALSE output instead".
We now have an expression that always evaluates to either TRUE or FALSE with no errors, so we can use the IF...THEN...ELSE structure around it. So we end up with this:
if [the above evaluation] = true then [do this] else [do this]
Hope this makes sense.
Pete
Proud to be a Datanaut!
@BA_Pete Hi Pete, got it, thanks!!!! 🙂
Very clear. So actually Powerquery reads it logically in the order of operations even without brackets...
I learned something today - THANKS!!! 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
17 | |
11 | |
9 | |
8 |
User | Count |
---|---|
42 | |
24 | |
21 | |
13 | |
12 |