Imagine you have a paragraph of text and you want to replace all occurrences of {four, normal, mysterious, nonsense} with {six, casual, confounding, handbags}. How would you do that?
You could use SUBSTITUTE() formula, but you need to nest four of them (as we need to replace four values with another four). But what if you have larger set of find / replacements?
Worry not, you can use Power Query to transform original text to new one by replacing all matching values.
In this page, learn how to do that with the excellent List.Accumulate() Power Query function.
The set up for multiple find / replace
Let’s start with two tables – mytext with the original text values and replacements with rules for replacement.
I have included a snapshot of these tables below.
Power Query transformations
Start by loading both of these tables to Power Query. We will transform mytext table to add a column with replaced text.
Before we could do the transformations, you must learn 3 key concepts.
They are,
- List.Accumulate() Function
- Getting Lists from Table columns
- How to access individual list items
List.Accumulate() Function
List.Accumulate() function allows us to perform same action on items on a list and return a combined value.
A simple example for this is,
Imagine you have a list of 10 numbers {1..10}
You want to calculate the total of these numbers.
You can use List.Accumulate() to add them up. Of course, you can also use List.Sum() function, but this is a demo of List.Accumulate() dear.
Assuming the original list is in source, we can use List.Accumulate() to add up items like this:
=List.Accumulate(Source, 0, (state, current) => state+current)
The output of this step will be 55, sum of list with first 10 numbers.
🤯🤯🤯
What’s going on here?
List.Accumulate() is a cumulative function. It takes a list and a starting value (in our case these are source and 0) and applies a function on each of the list items while modifying the initial value.
Let’s see what that means for our list.
- We start with 0 (call this starting state)
- For each item in the list
- Update state to state + current (ie 0+1 initially, then 1+2, 3+3, 6+4…45+10)
- At the end of the list, it returns the final state value, ie 55.
How to write the third argument of List.Accumulate()?
The third argument of List.Accumulate() is a function with two parameters – state, current.
- State: this will be initially set to 2nd parameter of the function and changes every time List.Accumulate() moves down the list.
- Current: this will the value of current list item
Power Query functions are written in this fashion.
(parameter1, parameter2…) => function definition
So, for example (state, current) => state+current is a function that takes 2 parameters and returns their sum.
To learn more about List.Accumulate():
- Watch the video that accompanies this article. You will get clarity about List.Accumulate()
- Read Datachant’s excellent List.Accumulate examples page.
Getting Lists from Table columns
If you have a table named replacements with columns [Find] and [Replace] in Power Query, you can use below syntax to extract a table column as list.
replacements[Find]
tablename[Column name]
How to access individual list items
If you have a list named source in Power Query with 10 items, you can access 5th item of the list with this syntax.
source{4}
So, for example, if you want to get 3rd item of the [Find] column in replacements table, use:
=replacements[Find]{2}
Note: Power Query uses 0 base for lists. So first item of the list will be list{0}
Using List.Accumulate for mass Find / Replace
Now that you are familiar with key concepts necessary, let’s do some replacements.
Go to mytext query and insert a custom column. In this column, we will generated replaced text.
Write this formula:
List.Accumulate(
List.Numbers(0, Table.RowCount(replacements)),
[Text],
(state, current) =>
Text.Replace(state,
replacements[Find]{current},
replacements[Replace]{current}))
How does this formula work?
- We create a list of numbers from 0. The size of this list will be same as number of rows in replacements table.
- For our sample data, we get {0,1,2,3}
- We start with input [Text] column value
- We replace first replacements[Find] value with replacements[Replace] in [Text]
- We repeat 3 step three, while updating the state
- At the end of this process, we end up with [Text] that successively replaced all words in replacements table.
I know this formula is tricky to get your head around, but once you understand it, you will see the potential for other cool applications.
Please click here to download the multiple find replace example file
List.Accumulate explained - Video
If you are still 😕 and not sure how List.Accumulate magic works, then please watch this video. It goes in to greater detail about this beautiful M function. See it below or visit my YouTube channel for it.
More on Power Query...
Please check out below pages to learn more about List.Accumulate() in particular and PQ in general.
List.Accumulate examples:
- List.Accumulate unleashed – several examples [Datachant]
- Running totals with List.Accumulate [ExcelGuru blog]
- List.Accumulate syntax and reference page [PowerQuery Doc on MS]
Power Query examples for data cleanup:
19 Responses to “Free Invoice Template using Excel – Download”
Nice post! Invoicing for the small biz or solo entrepreneur is something I see a lot of interest in. Also there are great templates from http://office.microsoft.com/en-us/templates
This is awesome.
I would need a little more. e.g. say I generate a Inv. # 1 with all the details. Once done I can click a button all the relevant details gets stored in some table. Further, when i generate a new invoice those details gets stored in same table but just below the previous invoice.
Is their a way to do this?
I did create a solution you are looking for, however its wrapped in a larger 'Medical Scheduler' and it uses VBA, But you can Save, Update, Lookup, Email, Print & Apply Payments to the Invoice.
You are welcome to download it here:https://www.dropbox.com/s/2yvo0o2tgq9quhe/Medical_Massage_and_Salon_Application-Free.xlsm
The Invoice Items are created from the Appt. Types & Service Items table.
I would love all feedback from this
Thank you for sharing. I will definitely have a look at it.
Daily dose of Excel held a competition in 2005 for this same topic
It obtained 9 solutions which are shown:
http://dailydoseofexcel.com/archives/2005/10/27/invoice-app-the-results/
[…] http://chandoo.org/wp/2014/03/19/free-invoice-template/?utm_source=feedburner&utm_medium=email&a… […]
How can i removed Dollar Sign, As want to use this in india.
Please reply.
Also if possible then can i use Indian Rupee Sign and how?
Hi Chandoo,
Thanks for sharing this invoice template, Let me tell you this template will definitely help me since I got a process to handle where this invoice piece comes. Just a small doubt, can we store all the invoice details in PRODUCT & SERVICES sheet. So that whenever I select an invoice number from invoice sheet I can take print out and I can share it as well. Can we do that?? Since I will be dealing with this on monthly basis.
It would be great if you can help me with this.
Thanks in advance for your help!
Regards,
Gaurang Mhatre
Hi Chandoo,
I was thinking learning excel is quite tuff task but your blog proved me wrong. You made it very interesting. Thank you. Also the template you have provided for Invoice is very helpful to us.
Thanks thanks thanks.. Very helpful. 🙂
Hi i love the speadsheet but would like to ask how do i get it to add the description into the invoice as well
Hi Randy, I tried to download one of your link "https://www.dropbox.com/s/2yvo0o2tgq9quhe/Medical_Massage_and_Salon_Application-Free.xlsm" However, i found the link unavailable. Can you please help me get the new link or can you please send this VBA file on my Email-ID.
Hello Anuj,
Thanks for alerting me to the broken link. This one should work:
https://www.dropbox.com/s/gz89gshex1ad0ex/Medical_Massage_and_Salon_Application-Free.xlsm?dl=0
Please let me know if you have any questions.
Randy
Thank you so much Buddy. will check and revert you soon.
Hi, is there any chance that this can work with the "Products & Service" sheet outside of the Invoice sheet. I create multiple invoice files for the numerous clients. Updating the product sheet for each of them maybe a task. Hence, I want to create a MASTER FILE from which data can be picked up without having to insert new data in each of the invoice files.
Possible? Or am I asking for the moon 😉
Thank you so much for tutorial.
This example can be reviewed for the example of the advanced invoice that made with excel userform :https://youtu.be/Qr-4of-38DI
Good Day
i love this template may i ask if it could be modified to have the following
when you lookup a item code in the next column to the right it brings up the description then the quantity, unit cost, discount and then total otherwise i love the template
Item Code Description Quantity Unit Cost Discount Total
When creating an Invoice template in Excel are you able to utilize the auto row height and wrap feature when the cell is a merged cell? I need to have a number of cells merged together to allow for enough space to type in the description of work performed (lets say cells A-D are merged in each row) however it seems that I am unable to utilize the auto format feature. To work around this I have to manually increase the row height after each entry. Is there a better solution for this? Thank you!