How to number each occurrence of a substring in a cell in Power Query?

Question

I'm fairly new to Power Query and have hit a hiccup that's been bothering me all day. I've read multiple threads here and on the Power BI community and none has really cleared my question, and my logic suggests a few different options to achieve what I want, but my lack of experience blocks any solution I attempt.

Context: I'm building a database for product import/export into WooCommerce, eBay and other channels; which takes some inputs by the (non tech savyy) users in Excel and develops several of the required fields. One of those is the image file names for each product.

I have this columns (in a much larger query table):

| ImageBaseName    | ImageQTY |                    ImageIDs                            |
| product-name.jpg |    3     |   product-name.jpg product-name.jpg product-name.jpg   |
| other-product.jpg|    5     |other-product.jpg other-product.jpg...other-product.jpg |

And my desired output would be:

| ImageBaseName    | ImageQTY |                         ImageIDs                             |
| product-name.jpg |    3     |   product-name-1.jpg product-name-2.jpg product-name-3.jpg   |
| other-product.jpg|    5     |other-product-1.jpg other-product-2.jpg...other-product-5.jpg |

In fact I don't need the two first columns if I get the ImageIDs like that. The ImageBaseName column is generated from the input product name. The ImageQTY column is direct input by the user. The ImageIDs column I got so far is from using:

= Table.AddColumn(#"previous step", "ImageIDs", each Text.Trim(Text.Repeat ([ImageBaseName]&" ", [ImageQty])))

And these are the options I've considered thus far:

Option 1: Text.Combine(Text.Split ImageIDs and (somehow) count and number each item in the list) and concatenate it all back... Which would probably start like this: Text.Combine(Text.Split,,,

Option 2 Using the UI, splitting the ImageIDs by each space and by a high number of columns (as I don't know how many images each product will have, but probably no more than 12) and then assign a number suffix to each of those columns and then putting it all back together, but it feels messy as hell.

Option 3 Probably theres a clean calculated way to generate the numbered image base names based on the number in the second column, and then attach the .jpg at the end of each, but honestly I don't know how.

I'd like it to be on the same table as I am already dealing with different queries...

Any help would be gladly accepted.

I think that your best bet would be to create a function where you call the function using the ImageBaseName, ImageQTY and the return string would be your result. — Jim L
What do you mean... Like a "For" loop? Within Power Query? I'd rather to not include VBA in Excel directly because I can't trust the users to know how to enable macros when opening the file in their devices... — DGuzmanG
Users dont have to open VBA and run a macro for using UDFs(User Defined Functions). If someone insert a module in excel workbook and defines a function as in the answer and saves the workbook, the function will remain available forever in the workbook or its copies. even if they are shared/sent over email to other users. Users will just have to use it on excel sheet. Thus functions are different from macros. — Naresh

Wedge Wedge · Accepted Answer · 2020-03-06T20:02:27

Here's a chunky "uber step" piece of code you could put in a custom column given the ImageBaseName and ImageQty columns

Text.Combine
(
    List.Transform
    (
        List.Zip
        (
            {
            List.Repeat({Text.BeforeDelimiter([ImageBaseName], ".", {0, RelativePosition.FromEnd})},[ImageQTY])
            ,
            List.Transform({1..[ImageQTY]}, each "-" & Number.ToText(_) &".")
            ,
            List.Repeat({Text.AfterDelimiter([ImageBaseName], ".", {0, RelativePosition.FromEnd})}, [ImageQTY])
            }
        )
    , each Text.Combine(_)
    )
, " "
)

Summary is you create the components of your string as 3 lists (text before file type, numbers 1 through qty, text after file type). Then you use List.Zip which combines the three text components into their own lists. Then we convert those lists back to a single piece of text with List.Transform and Text.Combine.

How to number each occurrence of a substring in a cell in Power Query?

3 Answers