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
DGuzmanG -
Will this work for you?
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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Image Base Name", type text}, {"ImageQty", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform({1 ..Number.From([ImageQty])}, each Number.From(_))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Text.Insert([Image Base Name],Text.PositionOf([Image Base Name],".",Occurrence.Last),"-"&Text.From([Custom]))),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Image Base Name", "ImageQty"}, {{"Data", each _, type table [Image Base Name=text, ImageQty=number, Custom=number, Custom.1=text]}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Data],"Custom.1")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Data"})
in
#"Removed Columns"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
