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
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.