Forum Discussion
Replace a column value with dynamic value
| Name |
| Anu |
| Tomy |
| Mikhayel |
| Jan-25 |
| George |
| Thomas |
| Jerry |
I have a table with sample data like this, i want to replace the entire column with the value Jan -25, the value of month is dynamic. And it is not necessarily current month. How to perform this in power query
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
9 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswrVYrViVYKyc+tBDN8M7MzEitTc8Acr9SiIoiwe2p+UXoqRGlGfm5iMUQ+MU/XyFQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]), Repalce = Table.ReplaceValue(Source, null, List.Last(Source[Name]),(v,x,y)=>y,{"Name"}) in RepalceHi 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.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.
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 RepalcedHi Anonymous , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.Hi @Antmkjr , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.Hi @Antmkjr , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.
