Forum Discussion
Replace a column value with dynamic value
Hi Anonymous, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswrVYrViVYKyc+tBDN8M7MzEitTc8Acr8Q8XQMjMNM9Nb8oPRWiNiM/N7EYoiC1qAioLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]), Date = List.Select(Source[Name], (x)=> (try Date.FromText("01-" & x) otherwise false) is date){0}, Repalced = Table.TransformColumns(Source, {"Name", each Date, type text}) in Repalced
Hi Anonymous , thank you for reaching out to the Microsoft Fabric Community Forum.
Try this:
- Power Query Editor -> Add Column -> Custom Column
- Give the new column name and in formula give the dynamic value to be replaced. Click ok.
- Manage Parameters -> New Parameter
- Give name(current month), type: text & current value(Jan-25).
- Go to Transform tab -> Replace values -> Replace values
- In Value to find, leave it blank of give *, In replace with give current month
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.
Someway we are still hard coding Jan-25 in manage parameter. Suppose if have any other month we still cannot handle it dynamically
Hi Anonymous , thank you for reaching out to the Microsoft Fabric Community Forum.
Please try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswrVYrViVYKyc+tBDN8M7MzEitTc8Acr8Q8XQMjMNM9Nb8oPRWiNiM/N7EYoiC1qAioLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
// Extract the month dynamically from the first valid date in the column
FirstDate = List.Select(Source[Name], (x) => (try Date.FromText("01-" & x) otherwise false) is date){0},
MonthYear = Text.Start(FirstDate, 6), // Extracts "Jan-25" from the date
// Replace all values in the "Name" column with the dynamically extracted month
Replaced = Table.TransformColumns(Source, {"Name", each MonthYear, type text})
in
Replaced
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.