Replace a column value with dynamic value

[Antmkjr]

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

[v-hashadapu]

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.

[v-hashadapu]

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.

[v-hashadapu]

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.

[dufoq3 · Accepted solution]

Hi @Antmkjr, 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

[v-hashadapu]

Hi @Antmkjr , thank you for reaching out to the Microsoft Fabric Community Forum.

Try this:

1. Power Query Editor -> Add Column -> Custom Column
2. Give the new column name and in formula give the dynamic value to be replaced. Click ok.
3. Manage Parameters -> New Parameter
4. Give name(current month), type: text & current value(Jan-25).
5. Go to Transform tab -> Replace values -> Replace values
6. 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.

[Antmkjr]

Someway we are still hard coding Jan-25 in manage parameter. Suppose if have any other month we still cannot handle it dynamically

[v-hashadapu]

Hi @Antmkjr , 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.

[ZhangKun]

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
    Repalce

[Antmkjr]

Thank you for your input. But Jan -25 doesn't necessarily come in the last row always