Forum Discussion

masplin's avatar
masplin
Impactful Individual
2 years ago
Solved

Tricky text extraction in Power Query

Hi   I have a data string [Latest Financials] that looks like this   {'vat': ['20.00', '85.26'], 'share': {'price': '426.31', 'num_parties': 4}, 'total': '511.57', 'subtotal': '1705.24', 'drug_te...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    masplin < Try using this m-code in custom column

     

    = Table.AddColumn(#"PreviousStepName", "Drug Test Fee", each
    let
    sourceText = [Latest Financials],
    startPos = Text.PositionOf(sourceText, "drug_test_fees"),
    afterKey = Text.Middle(sourceText, startPos + Text.Length("drug_test_fees") + 4),
    feeText = Text.BeforeDelimiter(afterKey, ",")
    in
    if startPos > 0 then Text.Trim(Text.Select(feeText, {"0".."9", "."})) else null
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

masplin < Try using this m-code in custom column

 

= Table.AddColumn(#"PreviousStepName", "Drug Test Fee", each
let
sourceText = [Latest Financials],
startPos = Text.PositionOf(sourceText, "drug_test_fees"),
afterKey = Text.Middle(sourceText, startPos + Text.Length("drug_test_fees") + 4),
feeText = Text.BeforeDelimiter(afterKey, ",")
in
if startPos > 0 then Text.Trim(Text.Select(feeText, {"0".."9", "."})) else null
)

masplin's avatar
masplin
Impactful Individual
2 years ago

Thats great but just to understand

 

Start pos find where the "d" is

afterkey is text everythnig to right of drug_test_fees + 4 charachters? so captures the numerical value

feeText  This is now the bit 646.00', [['Hair strand testing x4 sections (3 scalp, 1 body)', ['Cannabis', 'Cocaine', 'Opiates', 'Amphetamine', 'MDMA (Ecstasy)'], '646.00']]], 'coll etc   so looks left of the first comma and trims it

 

Text.select only picks out numbers and "." 

 

Genius thanks so much