Extraction with Text Between Delimiters not working

[data123]

Hello,

I am trying to extract full names for a column. The coulmn is in case table. I want to extract the names and count the number of cases against each name. I am using text between delimters function to extract the names. First delimiter- "@" Second delimiters- "Space."

When I give value of 1 to the 'number of end delimters to skip' it doesn't recognize the space between the first name and last name and extracts an extra word after the name.

When I give value of 0 to the 'number of end delimters to skip' it does recognize the space between the first name and last name and extracts only the first name.

What should I do so that I can extract just the full name(Both first and last).  I have attached screenshots below for reference.

Thanks,

Shubham Keshri.

[edhans]

See this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYwxCsMwEAS/sqjOI0RKg5vYnXCh4JMlkO/MScHo91acIrDV7DDOmTmmgr6gssMOFAKmPdUIYdRIUDpEq1keztjRa8MgTAWnSqUupHJfdyX6ApbOePv5T3lj/OgR218iXnF++3byOTe8qLHktWeWCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.BetweenDelimiters([Column1], "@", " ", 0, 1))
in
    #"Added Custom"

It works on my sample data just fine.

[data123]

Hi edhans,

I am using the exact same code you used but it doesn't work for me. It exacts an extra word after the last name.

Source = Table.Combine({#"Case Feed Report_sk_Yr2017-19", #"Case Feed Report_sk_Yr2020", #"Case Feed Report_sk_Yr2021"}),
#"Duplicated Column" = Table.DuplicateColumn(Source, "Body", "Body - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Body - Copy", "ETL"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date/Time Opened", type datetime}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([ETL], "@", " ", 0, 1), type text),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", "First Name"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Case Number", "Closed", "Date/Time Opened", "Date/Time Closed", "Body", "ETL", "First Name"})
in
#"Reordered Columns"

[edhans]

Then your data isn't the same. Mine is 100% spaces. You might have hard spaces (ASCII code 160) or some other weird char that looks like a space (char 32) but isn't. You'd have to share a data file via OneDrive/Dropbox (Excel or CSV) for me to test with as pasting it in the forum will convert the ASCII.

[data123]

Hi Edhans,

I have uploaded the excel file on drop box. The link to the file is below.

https://www.dropbox.com/scl/fi/4ehf8bbw4gm4l6mko90ss/Escalation-Data.xlsx?dl=0&rlkey=86aj3zyprl3k8ny...

Thanks,

Shubham

[data123]

Hi Edhans,

There is some problem with the above link. It just wont open. I am attaching a new dropbox link below. Let me know if you can access the file.

https://www.dropbox.com/scl/fi/4ehf8bbw4gm4l6mko90ss/Escalation-Data.xlsx?dl=0&rlkey=86aj3zyprl3k8ny... 

[edhans]

I have it. looking at it now @data123 

[edhans · Accepted solution]

As I suspected @data123 - those aren't spaces. Look at this:

The character after the Y in Ashley's name is a space - ASCII code 32.

The characters after the S in her last name is char 63 and char 160. Char 160 is an HTML  non-breaking space. 63 is a quesion mark, which isn't showing. It is because in reality it is a high ASCII non-printable character. I can select it and copy/paste it, but as you can see below, I cannot see it. Excel cannot see it properly either which is why it reports it as 63. But Power Query can. It is ASCII character 8203 which is being inserted by your software.

So visually you and I see a space after "hodges" but Power Query sees that invisible character, plus the non-breaking space (ASCII 160)

So I added to Replace statements after the initial import, and before the Text.BetweenDelimiters function:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,Character.FromNumber(8203),"",Replacer.ReplaceValue,{"ETL"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",Character.FromNumber(160)," ",Replacer.ReplaceText,{"ETL"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each Text.BetweenDelimiters([ETL], "@", Character.FromNumber(32), 0, 1))
in
    #"Added Custom"

Likely only the 2nd one is needed - it will replace all non-breaking spaces with a space (160 to 32) then Text.BetweenDelimiters works.

I was doing some testing, so you can replace:

Text.BetweenDelimiters([ETL], "@", Character.FromNumber(32), 0, 1)

with

Text.BetweenDelimiters([ETL], "@", " ", 0, 1)

It is the same thing.

[data123]

Hi @edhans,

Thank you so much! Its working for me.

Thanks,

Shubham.

[edhans]

Glad to help @data123 - those "invisible" characters can be the toughest to undersand and figure out why it seems the code isn't working.

[data123]

@edhans Yes they are!