How to get and split specific number from value

[Naldi]

Hi everyone,

Im new in PBI. I have one column name is "col" with value 9122653911221084122066122. How to I get and split specific number from that column like table below. The specific number is "122". Every number 122 gets 6 characters after that.

[mussaenda · Accepted solution]

Hi @Naldi ,,

[Naldi]

Thank for your help @mussaenda , I appreciate.. 🙂

[Royel]

Hi @Naldi 

At first, go to the power query editor. then follow the instructions below 

Step 01: Change the Data Type number to text 

Click Add Column and then select Custom Column and pest the code 

= Table.TransformColumnTypes(#"Changed Type",{{"col", type text}})

 Output:

Step 02: Split first match 

Click Add Column and then select Custom Column and pest the code, then change type to text

= Table.AddColumn(#"Changed Type1", "split_col_1", each if Text.Contains([col], "122") then Text.Middle([col], Text.PositionOf([col], "122"), 6) else null)

 Output: 

Step 03: Split second match 

Click Add Column and then select Custom Column and pest the code, then change type to text

= Table.AddColumn(#"Changed Type4", "split_col_2", each 
    let
        textToSearch = [col],
        startPos = Text.PositionOf(textToSearch, "122"),
        secondStartPos = if startPos >= 0 then Text.PositionOf(Text.Middle(textToSearch, startPos + 3), "122", Occurrence.First) + startPos + 3 else -1
    in
        if secondStartPos >= 0 then Text.Middle(textToSearch, secondStartPos, 6) else null
)

Output:

Step 04: Split third match 

Click Add Column and then select Custom Column and pest the code, then extract the list and change data type to text

= Table.AddColumn(#"Changed Type2", "split_col_3", each Table.AddColumn(#"Changed Type4", "third", each 
    let
        textToSearch = [col],
        startPos = Text.PositionOf(textToSearch, "122"),
        secondStartPos = if startPos >= 0 then Text.PositionOf(Text.Middle(textToSearch, startPos + 3), "122", Occurrence.First) + startPos + 3 else -1,
        thirdStartPos = if secondStartPos >= 0 then Text.PositionOf(Text.Middle(textToSearch, secondStartPos + 3), "122", Occurrence.First) + secondStartPos + 3 else -1
    in
        if thirdStartPos >= 0 then Text.Middle(textToSearch, thirdStartPos, 6) else null
)[third])

Output:

Regards
Royel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.