Mark, this solution is perfect for my application because I have a long list of standard e-mail replies for my client's FAQ's. When I import a customer's information into the array, the generated replies include their specific name and order information. From there, all I have to do is copy/paste the generated text into my email reply instead of re-typing the same message over and over, and there are no worries of misspelling or entering a number incorrectly. MASSIVE time saver for me, the efficiency is off the charts. I was using nested SUBSTITUTE functions until now, but the formula has become too long and tedious to update, and feels like a bad workaround. I was looking for a cleaner and more dynamic formula, and the one you presented in this video is exactly what I've been looking for! Thank you!!!
Thanks for posting another very interesting and practical tutorial in Excel. Since I started learning Power Query a scant three weeks ago, I am trying to use it in as many situations as possible.😃 I tried to replicate the functionality demonstrated in the video using PQ. I made a custom function that uses List.Accumulate() to make successive substitutions of the placeholders in the input string with the corresponding replacement values as listed in the table, conceptually mimicking (I think) the Excel REDUCE() function that you explained how to use. As I am far from proficient in PQ M code, my custom function is probably horribly inefficient and inelegantly composed, But, based on my very limited testing, it does seem to work. If nothing else, I had fun attempting to write some custom M code! Thank you kindly. ==================================================== // fxDynamicText() // Replace placeholders in input text, delimited by {, and }, // with values from the FindReplace table. (input as text) => let // From the FindReplace table, create a list of lists {, } // Have to case each element of Mapping as a list itself before using it in the // accumulator function. Mapping = Table.ToList(FindReplace, Combiner.CombineTextByDelimiter(",")), // Transform each element of Mapping into a list because initially each // element is just a text string with no structure. We require an indexed // sequence of items, so we can replace occurrences of the first element // with the value in the second element. Mapping_List = List.Transform(Mapping, each Text.Split(_, ",")), // The "seed" (2nd argument) is the original input string containing placeholders for // dynamic text substitution. Source = List.Accumulate(Mapping_List, input, (state, current) => Text.Replace(state, current{0}, current{1})) in Source My input string was: "My {sentence} has {placeholders} with {dynamic_content}. It is {month} {year} and I'm enjoying learning about {subject} on the {youtube channel} TH-cam channel." My output string was: "My office has a computer with Excel 365. It is July 2023 and I'm enjoying learning about Power Query on the Excel Off The Grid TH-cam channel." 😎
You stole my thunder... I've got a Power Query video with a List.Accumulate solution coming soon. It's not as tidy as yours, but a similar concept. If you're using List.Accumulate, then I think you're probably a pretty solid PQ user already.
This is great, thank you for sharing. Do you know if it's possible to replace the 'Offset' function within this formula? I try to avoid volatile functions where possible. I attempted to use 'Index' instead but that didn't work. I think there's no alternative but thought I'd ask!
Here are a few ideas: Standardizing a list of names (e.g. Wicks Ltd, Wick's Ltd and Wicks Ltd, can all be changed to just Wicks Ltd) - I've got a PQ video coming out with a similar solution soon. Building a complex sentences without a lot of concatenating for dynamic values Changing product descriptions - e.g. products were called "Premium Member", "Standard Member", "Economy Member" in the IT system, but the company now calls them "Diamond Member", "Platinum Member", "Standard Member" Cleaning special characters (e.g. changing "-", ":" and "!" for blank values) If you don't have to deal with text very often, then I agree. But if you do have to deal with text, then there are definitely uses.
Watching the video inspired to play with different functions. I've just used this function to replicate the countif function where two things needed to be true. The offset was used in a nested if statement to check the value of a cell in the row above: =REDUCE(0,ProjectCompCodes,LAMBDA(a,v,IF(v=HI6,IF(OFFSET(v,-1,0)=I6,a+1,a),a))) 0 is the starting value that accumulates ProjectCompCodes is just the name of a row of cells i.e. the array HI6 and I6 were just the values I needed to be true to count on if the value v in the array met the criteria and the value in the cell above v met the criteria. Similarly, you could replicate vlookup to call multiple values in a row using offset without using vlookup multiple times. Whether that is quicker/desirable I'm not knowledgeable enough to say, but this may be a starting point for considering what you might be able to accomplish. I also used the concat function to concatenate all the values in a list using this method. This is simpler than other methods I have employed.
This is perfect for my application because I have a long list of standard e-mail replies for my client's FAQ's. When I import a customer's information into the array, the generated replies now include their specific name and order information with zero spelling errors. From there, all I have to do is copy/paste the generated text into my email reply instead of re-typing the same message over and over, and there are no worries of misspelling or entering a number incorrectly. MASSIVE time saver for me, the efficiency is off the charts. I was using nested SUBSTITUTE functions until now, but the formula has become too long and tedious to update, and feels like a bad workaround. I was looking for a cleaner and more dynamic formula, and the one presented in this video is exactly what I've been looking for!
If I may ask a question here.... I have a series of cells in one column. Each cell contains between 3 to 10 sentences separated by commas (,). How could we extract sentences that contain either the word "University", "Universite", or "College"? Example cell B2 contains "University of Nippon, Institue of Technology, Nanyang University, Universite de Sorbone, Medical College, The Good University of Mine", I want to have in C2: "University of Nippon, Nanyang University, Universite de Sorbone, Medical College, The Good University of Mine"
#VALUE! Is Excel’s way of telling you there is an issue with the values in the formula e.g using a date function on a text value. Without looking at I can’t say exactly.
![[ ออกกอง ] วัยหนุ่ม 2544 โลกหลังลูกกรง..ทัวร์กองถ่ายหนังใน "คุกของจริง" | JUSTดูIT.](http://i.ytimg.com/vi/vB--aOkZCpw/mqdefault.jpg)
![Replace Multiple Column Values in One Step [Power Query Tips & Tricks]](/img/n.gif)
This is a great video. Really reduced my repetitive tasks
This is great - good to see a practical use for REDUCE, which is not one of the easier Lambda functions to get one's head around!
REDUCE is a tough one, hopefully we can find a few more use cases soon. 😁
Superb job as always Mark. Absolutely clear and bright. Thank you!!!
Glad you enjoyed it 😀
Mark, this solution is perfect for my application because I have a long list of standard e-mail replies for my client's FAQ's. When I import a customer's information into the array, the generated replies include their specific name and order information. From there, all I have to do is copy/paste the generated text into my email reply instead of re-typing the same message over and over, and there are no worries of misspelling or entering a number incorrectly. MASSIVE time saver for me, the efficiency is off the charts. I was using nested SUBSTITUTE functions until now, but the formula has become too long and tedious to update, and feels like a bad workaround. I was looking for a cleaner and more dynamic formula, and the one you presented in this video is exactly what I've been looking for! Thank you!!!
Great news. I’m glad I could help. 👍
Thanks for posting another very interesting and practical tutorial in Excel. Since I started learning Power Query a scant three weeks ago, I am trying to use it in as many situations as possible.😃 I tried to replicate the functionality demonstrated in the video using PQ. I made a custom function that uses List.Accumulate() to make successive substitutions of the placeholders in the input string with the corresponding replacement values as listed in the table, conceptually mimicking (I think) the Excel REDUCE() function that you explained how to use.
As I am far from proficient in PQ M code, my custom function is probably horribly inefficient and inelegantly composed, But, based on my very limited testing, it does seem to work. If nothing else, I had fun attempting to write some custom M code! Thank you kindly.
====================================================
// fxDynamicText()
// Replace placeholders in input text, delimited by {, and },
// with values from the FindReplace table.
(input as text) =>
let
// From the FindReplace table, create a list of lists {, }
// Have to case each element of Mapping as a list itself before using it in the
// accumulator function.
Mapping = Table.ToList(FindReplace, Combiner.CombineTextByDelimiter(",")),
// Transform each element of Mapping into a list because initially each
// element is just a text string with no structure. We require an indexed
// sequence of items, so we can replace occurrences of the first element
// with the value in the second element.
Mapping_List = List.Transform(Mapping, each Text.Split(_, ",")),
// The "seed" (2nd argument) is the original input string containing placeholders for
// dynamic text substitution.
Source = List.Accumulate(Mapping_List, input, (state, current) => Text.Replace(state, current{0}, current{1}))
in
Source
My input string was:
"My {sentence} has {placeholders} with {dynamic_content}. It is {month} {year} and I'm enjoying learning about {subject} on the {youtube channel} TH-cam channel."
My output string was:
"My office has a computer with Excel 365. It is July 2023 and I'm enjoying learning about Power Query on the Excel Off The Grid TH-cam channel." 😎
You stole my thunder... I've got a Power Query video with a List.Accumulate solution coming soon. It's not as tidy as yours, but a similar concept.
If you're using List.Accumulate, then I think you're probably a pretty solid PQ user already.
This is great, thank you for sharing. Do you know if it's possible to replace the 'Offset' function within this formula? I try to avoid volatile functions where possible. I attempted to use 'Index' instead but that didn't work. I think there's no alternative but thought I'd ask!
I also tried with INDEX for the same reason. I couldn’t make it work either.
If you find out, let me know.
This is really cool but I am struggling to find a practical example of when one would realistically use this. Any ideas?
Here are a few ideas:
Standardizing a list of names (e.g. Wicks Ltd, Wick's Ltd and Wicks Ltd, can all be changed to just Wicks Ltd) - I've got a PQ video coming out with a similar solution soon.
Building a complex sentences without a lot of concatenating for dynamic values
Changing product descriptions - e.g. products were called "Premium Member", "Standard Member", "Economy Member" in the IT system, but the company now calls them "Diamond Member", "Platinum Member", "Standard Member"
Cleaning special characters (e.g. changing "-", ":" and "!" for blank values)
If you don't have to deal with text very often, then I agree. But if you do have to deal with text, then there are definitely uses.
@@ExcelOffTheGrid Amazing thanks!
Watching the video inspired to play with different functions.
I've just used this function to replicate the countif function where two things needed to be true. The offset was used in a nested if statement to check the value of a cell in the row above:
=REDUCE(0,ProjectCompCodes,LAMBDA(a,v,IF(v=HI6,IF(OFFSET(v,-1,0)=I6,a+1,a),a)))
0 is the starting value that accumulates
ProjectCompCodes is just the name of a row of cells i.e. the array
HI6 and I6 were just the values I needed to be true to count on if the value v in the array met the criteria and the value in the cell above v met the criteria.
Similarly, you could replicate vlookup to call multiple values in a row using offset without using vlookup multiple times. Whether that is quicker/desirable I'm not knowledgeable enough to say, but this may be a starting point for considering what you might be able to accomplish.
I also used the concat function to concatenate all the values in a list using this method. This is simpler than other methods I have employed.
@@indianamathsman6444 Good work. Great skills 👍
This is perfect for my application because I have a long list of standard e-mail replies for my client's FAQ's. When I import a customer's information into the array, the generated replies now include their specific name and order information with zero spelling errors. From there, all I have to do is copy/paste the generated text into my email reply instead of re-typing the same message over and over, and there are no worries of misspelling or entering a number incorrectly. MASSIVE time saver for me, the efficiency is off the charts. I was using nested SUBSTITUTE functions until now, but the formula has become too long and tedious to update, and feels like a bad workaround. I was looking for a cleaner and more dynamic formula, and the one presented in this video is exactly what I've been looking for!
How can we do that in powerquery only if two columns are matching from two tables. Then find and replace only for those matching columns
If I may ask a question here.... I have a series of cells in one column. Each cell contains between 3 to 10 sentences separated by commas (,). How could we extract sentences that contain either the word "University", "Universite", or "College"?
Example cell B2 contains "University of Nippon, Institue of Technology, Nanyang University, Universite de Sorbone, Medical College, The Good University of Mine", I want to have in C2: "University of Nippon, Nanyang University, Universite de Sorbone, Medical College, The Good University of Mine"
THX A LOT
Interesting!
Thanks 😀
it gives the "#value!" error . Any idea why ? I have replicated exactly your table . my office version : 16.78 . Microsoft 360
#VALUE! Is Excel’s way of telling you there is an issue with the values in the formula e.g using a date function on a text value.
Without looking at I can’t say exactly.
Can we find your courses on Udemy?
We host our own courses on our own platform. You can find out information here: exceloffthegrid.com/courses/
That was really cleaver, it's quite hard to break down and visualize the gradual accumulation.
Thank you.
Yes, these new LAMBDA helper functions can be quite tricky.
Then you can use SCAN function instead of REDUCE to see step by step