Forum Discussion
Extraction with Text Between Delimiters not working
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.
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.
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"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.
Hi Edhans,
I have uploaded the excel file on drop box. The link to the file is below.
Thanks,
Shubham
