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

  • Question

  • Hello,

    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.jpg product-name.jpg product-name.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.



    • Edited by DGuzmanG Friday, March 6, 2020 4:14 AM
    Friday, March 6, 2020 3:49 AM

Answers

  • Thanks Imke!

    I've come accross your website and your comments so much during the last few days, that I can't believe I'm talking to the legend herself!!!

    I like the cleanliness of your solution, yet this time I solved it modifying a code suggested at Stack Overflow,  I removed the .jpg in a previous step (where the "ImageBaseName" was created) and it ended up looking like this:

    #"ImageIDsFromList" = Table.AddColumn (
       #"PreviousStep", 
       "ImageIDsList", 
       each 
        Text.Combine(
          List.Transform(
             List.Zip(
                {
                List.Repeat(
                   {[ImageBaseName]},
                   [ImageQTY]),
                List.Transform(
                      {1..[ImageQTY]}, each "-" & Number.ToText(_) & ".jpg"
                   )
                }
             )
           , each Text.Combine(_)
          )
          , ", "
         )
       )
    IN
    ImageIDsFromList



    Hope this will also serve you and someone else. And props to Stack Overflow user @Wedge, who provided the original code.

    Cheers,


    • Marked as answer by DGuzmanG Monday, March 9, 2020 7:37 PM
    • Edited by DGuzmanG Monday, March 9, 2020 8:52 PM
    Monday, March 9, 2020 7:37 PM

All replies

  • Hi,

    I would create a list of items in a new column  like so:

    {1..[ImageQTY]}


    In the next step I iterate through this list and create the strings (for each number):

    List.Transform ( 
        [List.OfImages], 
        (x) => Text.BeforeDelimiter ( [ImageBaseName] , ".jpg") 
                & Text.From ( x ) 
                & ".jpg" )

    This is the full code that you can paste into the advanced editor to follow the steps:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKTylNLtHNS8xN1csqSFfSUTIGYnRhBYICSrE60Ur5JRmpRbpQKahxpkCMIa6AIaKnp4epOzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ImageBaseName = _t, ImageQTY = _t, ImageIDs = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ImageBaseName", type text}, {"ImageQTY", Int64.Type}, {"ImageIDs", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "List.OfImages", each {1..[ImageQTY]}),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "ImageIDsWithNumber", each List.Transform ( [List.OfImages], (x) => Text.BeforeDelimiter ( [ImageBaseName] , ".jpg") & Text.From ( x ) & ".jpg" )),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Result", each Text.Combine([ImageIDsWithNumber], ", ")),
        Cleanup = Table.RemoveColumns(#"Added Custom2",{"List.OfImages", "ImageIDsWithNumber"})
    in
        Cleanup


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, March 6, 2020 6:21 AM
    Moderator
  • Thanks Imke!

    I've come accross your website and your comments so much during the last few days, that I can't believe I'm talking to the legend herself!!!

    I like the cleanliness of your solution, yet this time I solved it modifying a code suggested at Stack Overflow,  I removed the .jpg in a previous step (where the "ImageBaseName" was created) and it ended up looking like this:

    #"ImageIDsFromList" = Table.AddColumn (
       #"PreviousStep", 
       "ImageIDsList", 
       each 
        Text.Combine(
          List.Transform(
             List.Zip(
                {
                List.Repeat(
                   {[ImageBaseName]},
                   [ImageQTY]),
                List.Transform(
                      {1..[ImageQTY]}, each "-" & Number.ToText(_) & ".jpg"
                   )
                }
             )
           , each Text.Combine(_)
          )
          , ", "
         )
       )
    IN
    ImageIDsFromList



    Hope this will also serve you and someone else. And props to Stack Overflow user @Wedge, who provided the original code.

    Cheers,


    • Marked as answer by DGuzmanG Monday, March 9, 2020 7:37 PM
    • Edited by DGuzmanG Monday, March 9, 2020 8:52 PM
    Monday, March 9, 2020 7:37 PM