Replace Row Values Conditionally in Power Query, M
Uploaded by
rajeshps07Replace Row Values Conditionally in Power Query, M
Uploaded by
rajeshps07TODAY's MENU
HERE WE SERVE POWER QUERY M.
REPLACE MULTIPLE ROW VALUES
DID YOU
KNOW?
Introduction
In Today's Menu we will explore a more uncommon task: conditionally
replacing multiple row values conditionally. A follow-up question to a
challenge posted by Tanya McIlravy, to provide the ability to modify
entries based on specified business rules. Here's our sample dataset:
let
Source = Table.FromRows(
{
{"46828", "MDK24", "", ""},
{"46403", "", "", ""},
{"24359", "DND24", "", ""},
{"18791", "MDK24", "", ""},
{"13093", "MDK24", "", ""},
{"10073", "", "", ""},
{"45397", "LTA24", "", ""},
{"24982", "LTA24", "", ""},
{"10987", "DND24", "", ""},
{"32311", "", "", ""}
}, type table
[RECORD_ID=text, ACTION=text, VALUE1=text, VALUE2=text]
)
in
Source
Multiple Value Replacements on a Row
There is a lot of good content out there on replacing multiple values.
Simply put, that involves replacing A with B in one or more columns.
Extremely useful to combat inconsistencies, errors, and missing values.
Our requirement, however, is different; we need to conditionally replace
multiple field values simultaneously, with a predefined replacement.
More like: if true, replace A with B, C with D, and E with F on the row.
Here's what the simplified dataset looks like:
Replacement Conditions and Values
First, we have a table called replValues. This table contains the business
rules that need to be applied, it contains two columns:
StartsWith: A text value that needs to match the initial part of a
specific field in the data - for a replacement to occur.
Replacement: A record containing the replacement values for a
n-number of fields within the data.
If the string in the ACTION column StartsWith this text, row values in the:
ACTION, VALUE1 and VALUE2 fields have to be replaced.
Preparation: Buffering Lists for Lookups
Based on the replValues table we can create lists for both StartsWith
and Replacement columns, buffer them into memory using List.Buffer.
Buffering is a performance optimization to prevent Power Query from
re-evaluating these lists each time they're accessed.
An iterations list is created which contains all index positions, this gives
us a way to loop through each of the specified replacement rules.
lookFor = List.Buffer( replValues[StartsWith] ),
replWith = List.Buffer( replValues[Replacement] ),
iterations = List.Buffer( {0..List.Count( lookFor ) -1})
Utilizing the Record Merge to Update Fields
Here, List.Accumulate is used to iterate through each replacement rule
defined by iterations, a. For each item in the source table's record list, s,
it checks if the ACTION column starts with the value in lookFor{a}.
If it does, it merges the current row's record, _ with the corresponding
replacement record replWith{a}, effectively overwriting the ACTION,
VALUE1, and VALUE2 fields defined in the replValues table.
Replacer = Table.FromRecords(
List.Accumulate(
iterations,
Table.ToRecords(Source),
(s, a)=> List.Transform( s, each
if Text.StartsWith( _[ACTION], lookFor{a} )
then _ & replWith{a}
else _
)
)
)
Conclusion
This method enables you to dynamically search for, and replace parts of
the dataset based on specified business rules and requirements.
In our example the condition depended on the starting text of a specific
field. We leveraged lists, and the record merge to overwrite likenamed
fields from the left operand with the right, updating them all.
Did you learn something new or enjoy this content?
Consider liking, sharing, or following me or #pqtodaysmenu
I LOVE SOLVING
PRACTICAL PROBLEMS
WITH POWER QUERY/ M.
