Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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.
User | Count |
---|---|
7 | |
5 | |
5 | |
5 | |
4 |
User | Count |
---|---|
11 | |
11 | |
9 | |
7 | |
6 |