Forum Discussion

BizyB's avatar
BizyB
Regular Visitor
2 years ago
Solved

Extract every string occurrence with additional characters

hello experts have just begun having fun with the powerBI and have hit the below roadblock although sounds simple. please guide me where am going wrong!   problem statement:  am trying to extract...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    I suggest:

    in the Advanced Editor:

    #"Added Custom" = Table.AddColumn(Source, "Extract", each Text.Combine(
                List.Accumulate(
                    Text.PositionOf([Column1], "AD", Occurrence.All),
                    {},
                    (s,c)=> s & {Text.Middle([Column1],c,6)}),", "), type text)

     

    or in the UI:

    Text.Combine(
    List.Accumulate(
        Text.PositionOf([Column1], "AD", Occurrence.All),
        {},
        (s,c)=> s & {Text.Middle([Column1],c,6)}),
    ", ")

     

     

     

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

I suggest:

in the Advanced Editor:

#"Added Custom" = Table.AddColumn(Source, "Extract", each Text.Combine(
                List.Accumulate(
                    Text.PositionOf([Column1], "AD", Occurrence.All),
                    {},
                    (s,c)=> s & {Text.Middle([Column1],c,6)}),", "), type text)

 

or in the UI:

Text.Combine(
    List.Accumulate(
        Text.PositionOf([Column1], "AD", Occurrence.All),
        {},
        (s,c)=> s & {Text.Middle([Column1],c,6)}),
    ", ")