remove part of a string based upon the position of some text

[Powerwhite]

I'm trying to perform a string type function in power query in Excel, i would like to perform the following.

I would like to remove the name and replace it with some text that says "The Guest request in room ".

The name will always be followed by "in room", but the name will always be variable in length.

Any assistance appreciated.

MR JOHN SMITH in room 10 requested a coffee

[AlienSx]

Hi, try to replace your last step to

#"Replaced Value2"
  = Table.TransformColumns(
    #"Replaced Value1", 
    {
      "Column1.RoomNumber", 
      each Text.Replace(_, Text.BeforeDelimiter(_, " in room"), "The Guest request")
    }
  )

othewise provide some test data to work with.  

[Powerwhite]

Hi Alien,

apologies, this did work, it just had the wrong table column as the source, when i corrected this, it worked great. 🙂

However, if there are multiple text matches in the same data element, is there a way to change the subsequent instances?  Even if it means running the same code twice, i could change the first instance of the text from "in room" to "the room".

Thanks again, your efforts are appreciated.

[AlienSx]

Well, anything is possible with M 😀 Give us an example - some text string and explanation of what you want to get in the end. We are eager for challenges 😎

[Powerwhite]

Thanks again, you're a great helper :-D, 

Some times for what ever reason, similar data is repeated in the same data attribute, containing names.  Here is my example:

"Mr John Smith in room TB1 requested a Double room type
MR John Smith in room TB1 requested a room with a sea view

Mr John Smith in room TB1 would like a room away from the lift"

And i just really need to remove the personal names and put in some alternate text.

Thanks again, you're a star!

[AlienSx]

fx_replace = (org_text as text, delimiter as text, replace_with as text) as text =>
    let 
        text_to_replace = Text.BeforeDelimiter(org_text, delimiter),
        positions = Text.PositionOf(org_text, text_to_replace, Occurrence.All, Comparer.OrdinalIgnoreCase),
        split = Splitter.SplitTextByPositions(positions)(org_text),
        result = 
            Text.Combine(
                List.Transform( 
                    split, 
                    each Text.Replace(_, Text.BeforeDelimiter(_, delimiter), replace_with)
                )
            )
    in result,

#"Replaced Value2"
  = Table.TransformColumns(
    #"Replaced Value1", 
    {
      "Column1.RoomNumber", 
      each fx_replace(_, " in room", "The Guest request")
    }
  )

[Powerwhite]

Hi Alien,

i've tried an alternative method, which i think will work in principle, but just not sure i am structuring the argument correctly.  I am using the between delimeter function as this is a way of pin pointing the person name that can then be replaced by another text.  This is my code to achieve this, but really struggling to make it work, if anyone has any thoughts?

This is the data i am running it across:

MRS JOAN SMITH in room DB1 requested a Double room type
Booking is Non-Cancellable as Arrival Date is 27/03/23, Like for Like Amendments Only

#"Replaced Value4"
= Table.ReplaceValue(
#"Reordered Columns", each Text.BetweenDelimiters(_, "MR" , "in room"), "The Guest request",Replacer.ReplaceValue,{"Column1.Inhouse_InHouseGuest"})
in
#"Replaced Value4"

[AlienSx · Accepted solution]

HI, use Table.TransformColumns instead

let
    #"Reordered Columns" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYoxD4IwFAb/yhdmDQqDc5FBjUAibshQ5WkaynvaFhP+vRKGSy65a5qouNQ4VapEXRyvBxiGExmQZ1s4+ozkA3XQyGW8W1pamN5040ykN/yC8SiF13vND7JWz5f2UM6Zr7bIdaB5SXbxJo2TdIWz6QlPcYuogbj7EzwqtlPUtj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.Inhouse_InHouseGuest = _t]),
    #"Replaced Value4" = 
        Table.TransformColumns(
            #"Reordered Columns",
            {
                "Column1.Inhouse_InHouseGuest",
                each 
                    let 
                        prefix = Text.BeforeDelimiter(_, " "),
                        text_to_replace = Text.BetweenDelimiters(_, prefix, " in room "),
                        replacement = Text.Replace(_, text_to_replace, " The Guest request")
                    in replacement
            }
        )
in
    #"Replaced Value4"

[Powerwhite]

Hey @AlienSx 

You nailed it and gave me an education at the same time, many thanks for your perseverance my friend. 🙏👋

[Powerwhite]

Hi Alien,

Many thanks again for taking the time on this, very much appreciated.  That didn't quite work.  It firstly threw errors if there was a null in the field, but i managed to overcome that.  However, whilst it did correctly make the first instance change to the data, the original name was still present in the data for the subsequent instances.

Not sure if you have any other thoughts on this?

Thanks again.

[AlienSx]

Hi, @Powerwhite nulls - yeah, I did not check for null as well as other non-text values. But you fixed that - kudos! Regarding "no changes" issue - see what you have after "in" in your query. If you copy-pasted the code then you probable still has

in
   #"Replaced Value1"

there. Or if you use the code in the middle of the query - make sure that your next step uses correct step name (the one with transformation). 

[Powerwhite]

Hi Alien,

i replaced the next step values so i think that is OK.  here is the snippet from where i have added it:

#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"Room Notes",Replacer.ReplaceValue,{"Column1.Inhouse_Preferences"}),

fx_replace = (org_text as text, delimiter as text, replace_with as text) as text =>
let
text_to_replace = Text.BeforeDelimiter(org_text, delimiter),
positions = Text.PositionOf(org_text, text_to_replace, Occurrence.All, Comparer.OrdinalIgnoreCase),
split = Splitter.SplitTextByPositions(positions)(org_text),
result =
Text.Combine(
List.Transform(
split,
each Text.Replace(_, Text.BeforeDelimiter(_, delimiter), replace_with)
)
)
in result,

#"Replaced Value3"
= Table.TransformColumns(
#"Replaced Value2",
{
"Column1.Inhouse_Preferences",
each fx_replace(_, " in room", "The Guest request")
}
)

in
#"Replaced Value3"

Thanks again.

[Powerwhite]

Thanks again Alien, really appreciate your input here.

This didn't have the desired effect, no text actaully changed?  Here is an example of what id like to do to the  column in the table:

Current value = 

MR JOHN SMITH in room QB1 requested a Double room type

Would like the new value it the column = 

Guest requests in room QB1 requested a Double room type

Basically removing names.  i tried using text.removerange but struggled to get the syntax.  If i was coding this normally, i would search the string to find the position of "in room", take that value of the position and delete all chars up to that point and then concatenate the new text to the beginning, but struggling to apply the same here.

Thanks again.

[AlienSx]

look, this code is simple and it works

let
    sample_text = "MR JOHN SMITH in room QB1 requested a Double room type",
    replace = Text.Replace(sample_text, Text.BeforeDelimiter(sample_text, " in room"), "The Guest request")
in  replace

corresponding function looks like this

(sample_text) => Text.Replace(sample_text, Text.BeforeDelimiter(sample_text, " in room"), "The Guest request")

You want to transform text in one of the columns in one of your tables. Okay. Read an article about Table.TransformColumns on MS site and use this code to modify your table. If you are still struggling - please provide a sample file. Thanks! 

[AlienSx]

Hello, @Powerwhite 

Text.Replace( original_text, Text.BeforeDelimiter(original_text, " in room"), "The Guest request" )

[Powerwhite]

Hi AlienSx,

thanks for your response, still struggling with this as the text sits in a column in a table.  Here is my code snippet, all works well until the last line of code (including your suggestion).  I have probalby missed something to make it applicable to a list?

let
hotelid = GetValue("hotelcell"),
url = GetValue("envurl"),
subscription = GetValue("subscription"),
Source = Json.Document(Web.Contents(url, [Headers=[#"Ocp-Apim-Subscription-Key"=subscription, HotelCode=hotelid, RecordCountLimit="1000", Accept="application/json"]])),
#"Parsed JSON" = Json.Document(Source),
#"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"HotelID", "RoomNumber", "Floor", "RoomType", "RoomStatus", "FrontOfficeStatus", "RoomClass", "RoomFeatures1", "RoomFeatures2", "RoomFeatures3", "RoomFeatures4", "RoomFeatures5", "Inhouse_ConfirmationID", "Inhouse_Preferences", "Inhouse_AdultGuests", "Inhouse_ChildGuests", "Inhouse_LinenChange", "Inhouse_LNSpecials0", "Inhouse_LNSpecials1", "Inhouse_LNSpecials2", "Inhouse_LNSpecials3", "Inhouse_RoomFeatures1", "Inhouse_RoomFeatures2", "Inhouse_RoomFeatures3", "Inhouse_RoomFeatures4", "Inhouse_RoomFeatures5", "Inhouse_ArrivalDate", "Inhouse_DepartureDate", "Inhouse_InHouseGuest", "Inhouse_TracesDept1", "Inhouse_TracesText1", "Inhouse_TracesDept2", "Inhouse_TracesText2", "Inhouse_TracesDept3", "Inhouse_TracesText3", "Dep_ConfirmationID", "Dep_Preferences", "Dep_AdultGuests", "Dep_ChildGuests", "Dep_LinenChange", "Dep_LNSpecials0", "Dep_LNSpecials1", "Dep_LNSpecials2", "Dep_LNSpecials3", "Dep_RoomFeatures1", "Dep_RoomFeatures2", "Dep_RoomFeatures3", "Dep_RoomFeatures4", "Dep_RoomFeatures5", "Dep_ArrivalDate", "Dep_DepartureDate", "Dep_InHouseGuest", "Dep_TracesDept1", "Dep_TracesText1", "Dep_TracesDept2", "Dep_TracesText2", "Dep_TracesDept3", "Dep_TracesText3", "Arr_ConfirmationID", "Arr_HotelID", "Arr_RoomType", "Arr_Preferences0", "Arr_Preferences1", "Arr_Preferences2", "Arr_Preferences3", "Arr_RoomFeatures1", "Arr_RoomFeatures2", "Arr_RoomFeatures3", "Arr_RoomFeatures4", "Arr_RoomFeatures5", "Arr_AdultGuests", "Arr_ChildGuests", "Arr_ArrivalDate", "Arr_DepartureDate", "Arr_AllocatedRoom", "Arr_GuestNotes", "Arr_TracesDept1", "Arr_TracesText1", "Arr_TracesDept2", "Arr_TracesText2", "Arr_TracesDept3", "Arr_TracesText3"}, {"Column1.HotelID", "Column1.RoomNumber", "Column1.Floor", "Column1.RoomType", "Column1.RoomStatus", "Column1.FrontOfficeStatus", "Column1.RoomClass", "Column1.RoomFeatures1", "Column1.RoomFeatures2", "Column1.RoomFeatures3", "Column1.RoomFeatures4", "Column1.RoomFeatures5", "Column1.Inhouse_ConfirmationID", "Column1.Inhouse_Preferences", "Column1.Inhouse_AdultGuests", "Column1.Inhouse_ChildGuests", "Column1.Inhouse_LinenChange", "Column1.Inhouse_LNSpecials0", "Column1.Inhouse_LNSpecials1", "Column1.Inhouse_LNSpecials2", "Column1.Inhouse_LNSpecials3", "Column1.Inhouse_RoomFeatures1", "Column1.Inhouse_RoomFeatures2", "Column1.Inhouse_RoomFeatures3", "Column1.Inhouse_RoomFeatures4", "Column1.Inhouse_RoomFeatures5", "Column1.Inhouse_ArrivalDate", "Column1.Inhouse_DepartureDate", "Column1.Inhouse_InHouseGuest", "Column1.Inhouse_TracesDept1", "Column1.Inhouse_TracesText1", "Column1.Inhouse_TracesDept2", "Column1.Inhouse_TracesText2", "Column1.Inhouse_TracesDept3", "Column1.Inhouse_TracesText3", "Column1.Dep_ConfirmationID", "Column1.Dep_Preferences", "Column1.Dep_AdultGuests", "Column1.Dep_ChildGuests", "Column1.Dep_LinenChange", "Column1.Dep_LNSpecials0", "Column1.Dep_LNSpecials1", "Column1.Dep_LNSpecials2", "Column1.Dep_LNSpecials3", "Column1.Dep_RoomFeatures1", "Column1.Dep_RoomFeatures2", "Column1.Dep_RoomFeatures3", "Column1.Dep_RoomFeatures4", "Column1.Dep_RoomFeatures5", "Column1.Dep_ArrivalDate", "Column1.Dep_DepartureDate", "Column1.Dep_InHouseGuest", "Column1.Dep_TracesDept1", "Column1.Dep_TracesText1", "Column1.Dep_TracesDept2", "Column1.Dep_TracesText2", "Column1.Dep_TracesDept3", "Column1.Dep_TracesText3", "Column1.Arr_ConfirmationID", "Column1.Arr_HotelID", "Column1.Arr_RoomType", "Column1.Arr_Preferences0", "Column1.Arr_Preferences1", "Column1.Arr_Preferences2", "Column1.Arr_Preferences3", "Column1.Arr_RoomFeatures1", "Column1.Arr_RoomFeatures2", "Column1.Arr_RoomFeatures3", "Column1.Arr_RoomFeatures4", "Column1.Arr_RoomFeatures5", "Column1.Arr_AdultGuests", "Column1.Arr_ChildGuests", "Column1.Arr_ArrivalDate", "Column1.Arr_DepartureDate", "Column1.Arr_AllocatedRoom", "Column1.Arr_GuestNotes", "Column1.Arr_TracesDept1", "Column1.Arr_TracesText1", "Column1.Arr_TracesDept2", "Column1.Arr_TracesText2", "Column1.Arr_TracesDept3", "Column1.Arr_TracesText3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1.RoomNumber", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,1000000,Replacer.ReplaceValue,{"Column1.RoomNumber"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Column1.RoomNumber", Order.Ascending}}),
#"Replaced Value1" = Table.ReplaceValue(#"Sorted Rows",1000000,null,Replacer.ReplaceValue,{"Column1.RoomNumber"}),
#"Replaced Value2" = Text.Replace( {"Column1.RoomNumber"}, Text.BeforeDelimiter({"Column1.RoomNumber"}, " in room"), "The Guest request" )
in
#"Replaced Value2"