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.

Answer 1

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.

Answer 2

Lets assume range Table1 contains two columns ImageBaseName and Quantity

Add column ... Index column... Right Click ImageBaseName Split Column...By Delimiter... --Custom--, use a period as the delimiter and split at Right-most delimiter. That will pull the image suffix off

Add Column ... Custom Column ... name it list and use formula ={1..[Quantity]} which will create a list of values from 1 to the Quantity

Click the double arrow at the top of the new list column and choose expand to new rows

Click-Select the list, Quantity, ImageBaseName.2, ImageBaseName.1 columns and Transform ... Data Type...Text

Add Column .. Custom Column .. name it Custom and use formula =[ImageBaseName.1]&"-"&[list]&"."&[ImageBaseName.2] to put together all the parts

Right-click Index Group By ... [x] Basic, Group By index, new column name ImageIDs, Operation count rows

That will generate code like this:

Table.Group(#"Added Custom1", {"Index"}, {{"ImageIDs", each Table.RowCount(_), type number}})

Use formula bar to change the formula as shown below. It will combine rows using , as a separator

Table.Group(#"Added Custom1", {"Index"}, {{"ImageIDs", each Text.Combine([Custom], ", "), type text}})

Full sample code is below that you can paste into Home .. Advanced Editor...

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "ImageBaseName", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"ImageBaseName.1", "ImageBaseName.2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "list", each {1..[Quantity]}),
#"Expanded list" = Table.ExpandListColumn(#"Added Custom", "list"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded list",{{"list", type text}, {"Quantity", type text}, {"ImageBaseName.2", type text}, {"ImageBaseName.1", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each [ImageBaseName.1]&"-"&[list]&"."&[ImageBaseName.2]),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Index"}, {{"ImageIDs", each Text.Combine([Custom], ", "), type text}})
in #"Grouped Rows"

There are probably many ways to combine all this into one uber step, but I thought I'd show the parts

Answer 3

Starting with this as Table1:

This M code...

let
    Source = Table1,
    SplitAndIndexImageIDs = Table.AddColumn(Source, "Custom", each Table.AddIndexColumn(Table.FromColumns({Text.Split([ImageIDs]," ")}),"Index",1)),
    RenameImageIDs = Table.AddColumn(SplitAndIndexImageIDs, "NewImageIDs", each Text.Combine(Table.AddColumn([Custom],"newcolumn",each Text.BeforeDelimiter([Column1], ".") & "-" &Text.From([Index]) & "." & Text.AfterDelimiter([Column1], "."))[newcolumn],", ")),
    #"Removed Other Columns1" = Table.SelectColumns(RenameImageIDs,{"ImageBaseName", "ImageQTY", "NewImageIDs"})
in
    #"Removed Other Columns1"

Should give you this result: