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.

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors