Bulk Find and Replace on partial cell values

Power Query
1

Hi Community,

I’m exploring the possibilities of replacing values in bulk via the process that @Melissa explains in her course of Applied Problem Solving with Power Query/M, so far I managed to get it to work on entire cell contents, but not on cells in which the code only has to replace part of the contents.

To be concrete: I’d like to bulk replace values in table columns where some rows are in a YYYYQQ notation, and others are in the YYYYMM format. I only want to replace the Q1, Q2, Q3, Q4 with each related last month, so that the entire column can be converted into a date format.

Is there a way to change the M code for bulk replace values without having to specify exact cell contents?

Here’s the current M code that I used in the .xlsx attached:

let
ReplRec = [Q1 = "03", Q2 = "06", Q3 = "09", Q4 = "12", 1Q = "-03", 2Q = "-06", 3Q = "-09", 4Q = "-12"],
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Report Date", "Report Month"),
    BulkReplaceValues = Table.ReplaceValue(#"Duplicated Column", each {[Report Month],[Report Date]}, each Record.FieldOrDefault(ReplRec, {[Report Month],[Report Date]}),Replacer.ReplaceValue,{"Report Month", "Report Date"})
in
    BulkReplaceValues

Dummy data.xlsx (15.3 KB)

2

Hi @Maikelshuvit,

Copy this into a new blank query and see if that works for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdC7DcAgDATQXVyDhG3yocxnAWrE/mtEgkSRrwhFyif5zpZLoY0cSRD2Wai6L+vA8cviMw8sA+vAuK977w4+MFjACsb8BJ7BC3gFJ2sOzUczp+cfr/t95z2v2TAaMluKpc1y/FlVLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Distributor = _t, #"Report Date" = _t]),
    ReplRec = [Q1 = "03", Q2 = "06", Q3 = "09", Q4 = "12", 1Q = "-03", 2Q = "-06", 3Q = "-09", 4Q = "-12"],
    BulkReplaceValues = Table.ReplaceValue( Source, each [Report Date], each 
        if Text.Contains( [Report Date], "Q" ) 
        then Text.Middle([Report Date], 0, Text.Length([Report Date])-2 ) & Record.FieldOrDefault(ReplRec, Text.End([Report Date], 2), [Report Date] )
        else [Report Date],Replacer.ReplaceText,{ "Report Date"})
in
    BulkReplaceValues

I hope this is helpful

2 Likes
3

Hello @Maikelshuvit ,

Did the response from @Melissa (Thank you) help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION.

Thank you

1 Like
4

Amazing! After splitting the code into various steps to see what actually is happening I think I get the trick and this will serve as a base for similar cases.

Thanks for the help @Melissa !

2 Likes