Extracting interest Rate from Text

Power Query
1

The attached file shows an example of text on rows. I need to know how to go about extracting the Interest rate from a row that contains a % and place the Interest rate value in a new column. The only consistent thing I can see is the % character comes immediately after the Interest rate Value. However, the number of characters before and after the decimal point varies.

Can someone assist me please.

Thank you

Book12.xlsx (8.8 KB)

2

See if this will help (all data transformation handled in the Excel file for this sample)

NOTE - I did change the due dates in the original data, so that I didn’t need to include a transformation step to handle the difference between date formats (my native date format is mm/dd/yyyy)Book12.xlsx (103.2 KB)

image

1 Like
3

@AllisterB,

@Heather beat me to it, but here’s my Power Query approach.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc1BCoJAFADQq3wG2sXM96cOLQupTUVE0EJdiH5kKGdCR6nbp9kBOsDjpak4uLJ4wN4N3NqGrYddbytja9jUbMt3ZimUEeICkp4hiBSGipC0yJepOLdmKDzDtrB3uHLbQMJP1xnfZRalng3FCleTCb7mxt14mBdXcJnskSvTN7M+Oc8jJUn0C2mtUE84/BcHUutoxlphPFkSef4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByEachDelimiter({"Due"}, QuoteStyle.Csv, true), {"Text.1", "Text.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type1", "Text.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Text.1.1", "Text.1.2", "Text.1.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Text.1.2", "Text.1.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Interest Rate"}, {"Text.1.1", "Funding Agency"}, {"Text.2", "Date"}}),
    #"Added Custom Column" = Table.AddColumn(#"Renamed Columns", "Custom", each let splitDate = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)([Date]), splitsplitDate0 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(splitDate{0}?) in Text.Combine({Text.Reverse(Text.Middle(Text.Reverse([Date]), 5, 1)), Text.Combine(splitsplitDate0, "/"), Text.End([Date], 5)}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Date"}})
in
    #"Renamed Columns1"

image

image
image792×174 9.94 KB

Hope this is helpful. Solution file attached. You’ll need to change the type on interest rate, but didn’t know if you wanted it as a percentage w/ 2 dec. places or decimal #.

4

Thank You Heather and Brian

Could you explain what the Character Transition code does…

It wasn’t in the sample sent earlier but the attached file shows a text that doesnBook12.xlsx (103.2 KB) 't work with Heather’s approach.

5

@AllisterB,

Character transition is another way of splitting text – either as it transitions from letter to number or vice versa. In this case once we stripped the date off the right-hand side of the interest rate, we used character transition to separate the funding agency from the interest rate on the left-hand side.

– Brian

6

Hi @AllisterB,

Just out of curiosity I tried “Column from Examples” (you can find that on the ‘Add Column’ tab on the ribbon). Which I never used before but it picked up both Date and Percentage generating this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    FromExample_Date = Table.AddColumn(#"Changed Type", "Custom", each let splitColumn1 = Splitter.SplitTextByDelimiter("% ", QuoteStyle.None)([Column1]) in Text.Combine({Text.Middle(splitColumn1{1}?, 4), Text.Middle(splitColumn1{0}?, 50, 10)}), type text),
    FromExample_Perc = Table.AddColumn(FromExample_Date, "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "#(lf)", " "), type text)
in
    FromExample_Perc

Because this uses text functions the results are all text values but you can change that of course

image
image867×203 8.63 KB

Here’s your sample file. eDNA - extract data from text string.xlsx (163.6 KB)
I hope this is helpful

7

Sorry Allister, I shouldn’t have assumed your data would always have the Due Date included, or that the format of the fields would be the same.