Replace Values in Columns that Change

[negbc]

I have dynamically combined source files in power query and as a result is creates a source.name column which are the file names for each row, and it my case is the date the file was created. I was to replace the values in this column with a generic naming convention. However, the values in that column will change daily when the source files get refreshed with a new extract date. Is there a way in adanced editor to set this up? I tried to use the code below but it didin't work as needed. 

LW = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-7),"MMddyy"),

#"Replaced Value" = Table.ReplaceValue(#"Changed Type","inventory_"& LW &".csv","LW",Replacer.ReplaceValue,{"Source.Name"})

An example of the column values, to which the date suffix will change.

[jennratten · Accepted solution]

Please consider what is the expected result. Based on the script, it looks like your intent is to check to see if the file name includes the date for last week, and if so, replace the Source.Name value with only the formatted version of the date (MMddyy). If that really is your intent, then there are other ways of going this transformation that are more straight-forward. If that is not your intent, then I think the exlaination below will provide you with the insight needed to customize the script provided to meet your actual needs.

Your script says... 

Table.ReplaceValue(#"Changed Type","inventory_"& LW &".csv","LW",Replacer.ReplaceValue,{"Source.Name"})

If LW = "052424", then your script translates to:

• For every value in the Source.Name column, replace values equal to "inventory_052424.csv" with "LW" (literally the text LW, not the value of the LW variable).
•  Replacer.ReplaceValue means that the replacement should only be made if the entire cell value is match.   

If the intent is to replace instances of "inventory_052424.csv" with the value of the variable LW, then your script will need to be revised to what is shown below, however, that does mean that if the replacement is made and if there are other values in rows, then your column values will be inconsistent with each other.  Some will have a file name convention (like including the .csv) and others will just have the formatted date portion.

Table.ReplaceValue(Source, each [Source.Name], each if [Source.Name] = "inventory_" & LW & ".csv" then LW else [Source.Name], Replacer.ReplaceValue,{"Source.Name"})

When each row in a table needs to be evaluated and then conditionally modified, the 'each' keyword must be present to make that happen (in this circumstance). The Table.ReplaceValue function does not include a row-by-row evaluation by default; for efficiency, it evaluates a list of old values against a list of new values.   

Syntax:

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

If you still need more assistance, please provide an explaination or example of the expected value.  Thanks!

[negbc]

Yes, thank you! I got it working is the first code you provided.

[jennratten]

Hi @negbc - Have you had a chance to see if this works for you?  Please consider clicking Accept as Solution if this answered your question.  Thanks!

[v-cgao-msft]

Hi @negbc ,

Please try:

Table.TransformColumns(#"Changed Type",{"Source.Name", each "inventory_"& LW &".csv"})

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[negbc]

How would that work if I needed to replace "inventory_"& LW &".csv" with "LW"?