cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lukitoperkoso
Regular Visitor

Replace Value But Not the First Match

Hi, I am trying to clean a manual input file.

So, I have a cell of one column with content like this: abc123abc456abc789

(1) and I want to replace the "abc" to become like this: abc123, abc456, abc789 (so excluding the first "abc" to be replaced).

(2) the next step is I want to split the content to be like this:

column 1>column 123column 456column 789
abc123, abc456, abc789 abc123abc456abc789
abc456, abc789  abc456abc789
abc123, abc789 abc123 abc789

 

Please be advised that I would like to have each of the steps get a solution.

Hope I can get the answer to it here.

Thanks all!

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

Solve your two problems at one go

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyBpImpmZA0tzCUilWByyMIQBRBxaIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
    #"Removed Nulls" = List.Transform(Table.ToRows(#"Split Column"), each Table.FromRecords({List.Accumulate(_, [], (s,c) => if not (c is null) then Record.AddField(s, Text.Select(c, {"0".."9"}), c) else s)})),
    #"Combined Tables" = Table.Combine(#"Removed Nulls")
in
    #"Combined Tables"

 

Screenshot 2021-06-20 114153.png

 

PS: my favorable solution with regular expression

Screenshot 2021-06-20 132625.png

View solution in original post

3 REPLIES 3
CNENFRNL
Super User III
Super User III

Solve your two problems at one go

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyBpImpmZA0tzCUilWByyMIQBRBxaIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
    #"Removed Nulls" = List.Transform(Table.ToRows(#"Split Column"), each Table.FromRecords({List.Accumulate(_, [], (s,c) => if not (c is null) then Record.AddField(s, Text.Select(c, {"0".."9"}), c) else s)})),
    #"Combined Tables" = Table.Combine(#"Removed Nulls")
in
    #"Combined Tables"

 

Screenshot 2021-06-20 114153.png

 

PS: my favorable solution with regular expression

Screenshot 2021-06-20 132625.png

View solution in original post

mahoneypat
Super User IV
Super User IV

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxK1gNiQyNjEGViagaizC0slWJ14JLYRaFawKKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, "abc."), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1", "Column2", "Column3"}),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each "Column" & Text.AfterDelimiter([Value], "abc")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi Pat,

Thanks for answering. It seems to answer my second problem.

 

But how to solve my first problem? Actually, my initial string doesn't have a dot separating each "abc"s.  I still need to replace the "abc123abc456abc789" with "abc123, abc456, abc789" (replace "abc" to ", abc" but starting on the second abc/excluding the first abc). that way so that I can split using ", " delimiter to each individual column in an efficient manner.

 

also, do you have any solution to make it dynamic in case in the future I get more and more "abc" for the column split? because in my example, we only have 3 "abc"s and when I reproduce using 4 "abc"s I still have to open Settings in Split Column step to correctly split the 4th "abc".

 

Hope my questions and description are clear enough.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors