Extracting a number that is a percentage from text

[ktruninger]

I am new to Excel Power Query & love it! Question: does anyone know the formula to use to be able to make a separate column from the example below?
Dewalt Drill Yellow and Black 2.0% hardware store.
Question: How would I create a new column that only has the 2.0% from this text?
Thank you!
Ken

 

[lrobbo314]

How about this?

New__Document (60).xlsx
	A	B	C	D	E	F
2	Column1				Column1	Custom
3	Dewalt Drill Yellow and Black 2.0% hardware store.				Dewalt Drill Yellow and Black 2.0% hardware store.	2.0%
4	Kobalt Drill Blue 52.3%				Kobalt Drill Blue 52.3%	52.3%
5	Hitachi Drill Green 6.9%				Hitachi Drill Green 6.9%	6.9%
Sheet7

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.AddColumn(Source, "Custom", each Table.RemoveRowsWithErrors(Table.TransformColumns(Table.FromList(Text.Split([Column1]," ")),{{"Column1",each Number.From(_)}}))[Column1]{0}),
    Types = Table.TransformColumnTypes(Extract,{{"Column1", type text}, {"Custom", Percentage.Type}})
in
    Types

 

[ktruninger]

Thank you! Now I have to see if I can do this on my own!
Appreciate your help.
Ken

 

[lrobbo314]

This is a bit easier to follow or replicate.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Split = Table.AddColumn(Source, "Custom", each Text.Split([Column1]," ")),
    ToNumber = Table.TransformColumns(Split,{{"Custom", each try List.Transform(_, Number.From) otherwise null}}),
    Expand = Table.ExpandListColumn(ToNumber, "Custom"),
    RemoveErrors = Table.RemoveRowsWithErrors(Expand, {"Custom"}),
    Types = Table.TransformColumnTypes(RemoveErrors,{{"Column1", type text}, {"Custom", Percentage.Type}})
in
    Types

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    APrecent = Table.AddColumn(Source, "Percent", each Number.From(Text.Trim(Text.Select([Column1],{"0".."9",".","%"}),".")),Percentage.Type)
in
    APrecent