Forum Discussion
Solved
How to number each occurrence of a substring in a cell in Power Query?
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, an...
I'd simplify a bit
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKTylNLtHNS8xN1csqSFfSUTJWitWJVsovyUgt0oVKQ2VMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Image Base Name" = _t, ImageQty = _t]), AddImageIDs = Table.AddColumn( Source, "ImageIDs", each Text.Combine( List.Transform( {1..Number.FromText([ImageQty])}, (x)=> Text.BeforeDelimiter([Image Base Name],".") & "-" & Text.From(x) & "." & Text.AfterDelimiter([Image Base Name],".") ), " " ) ) in AddImageIDs
I'd simplify a bit
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKTylNLtHNS8xN1csqSFfSUTJWitWJVsovyUgt0oVKQ2VMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Image Base Name" = _t, ImageQty = _t]),
AddImageIDs = Table.AddColumn(
Source,
"ImageIDs",
each
Text.Combine(
List.Transform(
{1..Number.FromText([ImageQty])},
(x)=>
Text.BeforeDelimiter([Image Base Name],".") &
"-" &
Text.From(x) &
"." &
Text.AfterDelimiter([Image Base Name],".")
),
" "
)
)
in
AddImageIDs
SergeiBaklan @ChrisMendoza
Thanks for your answers guys!!
Like Sergei mentioned, the solution could be simplified and I used a similar solution to what he suggested, which came up on Stack Overflow:
What I did, was to remove the ".jpg" suffix in a previous step where it generated the ImageBaseName, so that it would just output what should be the filename. And it allowed me to generate the names based on the two first columns of my example (no "ImageIDs" required).
It looked 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