Split text by three characters before delimiter into rows

[brawnystaff]

In Power Query, I am looking to split text in one cell to rows by a known delimiter (in this case, a hyphen -). However, I am looking to split three characters before the hyphen. Any ideas? Code I have so far is a follows:

let
    Source = Csv.Document(File.Contents("C:\Users\Smith\Downloads\PQtext.txt"),1,"0,10000",ExtraValues.Ignore,1252),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
in
    #"Split Column by Delimiter"

Thanks.

 

[alansidman]

Please show some sample data 8-10 records using XL2BB. No pictures as we can not manipulate data in a picture.

 

[brawnystaff]

Was able to figure it out using Claude.ai. Solution is below.

let
    Source = Csv.Document(File.Contents("C:\Users\Smith\Downloads\PQtext.txt"),1,"0,10000",ExtraValues.Ignore,1252),
    
    // Define a custom splitting function
    SplitBeforeHyphen = (text as text) =>
        let
            SplitPositions = List.Transform(
                Text.PositionOf(text, "-", Occurrence.All),
                each _ - 3
            ),
            SplitText = List.Generate(
                () => [i = 0, part = Text.Start(text, List.First(SplitPositions))],
                each [i] < List.Count(SplitPositions),
                each [
                    i = [i] + 1, 
                    part = if [i] = List.Count(SplitPositions) then 
                               Text.Range(text, SplitPositions{[i]-1}, Text.Length(text) - SplitPositions{[i]-1})
                           else 
                               Text.Range(text, SplitPositions{[i]-1}, SplitPositions{[i]} - SplitPositions{[i]-1})
                ],
                each [part]
            )
        in
            SplitText,
    
    // Apply the custom splitting function
    SplitColumns = Table.TransformColumns(Source, {
        {"Column1", SplitBeforeHyphen, type list}
    }),
    
    // Expand the resulting list column
    ExpandedColumns = Table.ExpandListColumn(SplitColumns, "Column1")
in
    ExpandedColumns