First Try with Power Query

[Spyderturbo007]

Please be aware that I although I was watching videos most of the weekend about Power Query and Pivot Tables, I'm still very new. This is my first go around with a power query.

My eCommerce platform spits out a pile of garbage that I need to extract data from each month. My problem is that it isn't very consistent form SKU to SKU. What I'm trying to do is extract the dollar amounts from this pile of junk and add them together. Every amount I need to retain has a $ in front of it and any numbers that don't include a preceding $ can be ignored. I thought about trying to split columns based on the <br>, but the number of break points isn't consistent, so I wouldn't know what to keep and what to remove after the data was split, because some would have 3 columns and others might have 7 or 8.

Not all entries has a dollar amount, which is fine. They would just be blank or 0. The data is on a single line. Thanks so much for the help!

Badger C2 Performance Tee - White<br><b>Size:</b>&nbsp;Youth Medium<br><b>Color:</b>&nbsp;White<br><b>Personalization</b>&nbsp;Name:&nbsp;Smith - $5.00<br><b>Personalization</b>&nbsp;Number:&nbsp;22 - $2.50

Gildan DryBlend 50/50 Longsleeve<br><b>Size:</b>&nbsp;Adult 2XL - $2.00<br><b>Color:</b>&nbsp;Black

Gildan DryBlend 50/50 Longsleeve<br><b>Size:</b>&nbsp;Adult Large<br><b>Color:</b>&nbsp;Black

Gildan DryBlend 50/50 T-Shirt<br><b>Size:</b>&nbsp;Adult Large<br><b>Color:</b>&nbsp;Black

Carhartt Washed Duck Active Jac<br><b>Size:</b>&nbsp;Adult 2XL - $2.00<br><b>Color:</b>&nbsp;Black

Nike Dri-FIT Micro Pique 2.0 Long Sleeve Polo<br><b>Size:</b>&nbsp;Adult 2XL - $2.00<br><b>Color:</b>&nbsp;University Red

Port & Company Fleece Lined Beanie Cap<br><b>Color:</b>&nbsp;Athletic Red

District Perfect Tri Long Sleeve Hoodie<br><b>Size:</b>&nbsp;Adult 2XL - $2.00<br><b>Color:</b>&nbsp;Grey Frost

Carhartt Washed Duck Active Jac<br><b>Size:</b>&nbsp;Adult 2XL - $2.00<br><b>Color:</b>&nbsp;Black

Nike Dri-FIT Micro Pique 2.0 Long Sleeve Polo<br><b>Size:</b>&nbsp;Adult XL<br><b>Color:</b>&nbsp;Black

Nike Dri-FIT Micro Pique 2.0 Pocket Polo<br><b>Size:</b>&nbsp;Adult XL<br><b>Color:</b>&nbsp;University Red

Sport-Tek® Long Sleeve PosiCharge® Competitor™ Tee<br><b>Size:</b>&nbsp;Adult Small<br><b>Color:</b>&nbsp;True Navy<br><b>Design:</b>&nbsp;Design 4 (Medic 2) - $6.00<br><b>Employee Number</b>&nbsp;Employee Number:&nbsp;605836

Game Sportswear The Bravest Jacket<br><b>Size:</b>&nbsp;Adult Large<br><b>Color:</b>&nbsp;Navy<br><b>Personalization</b>&nbsp;Name:&nbsp;Jones - $5.00<br><b>Back Neck Design:</b>&nbsp;Yes - $12.00

 

[Spyderturbo007]

Just a quick note, once all the dollar amounts are extracted, I no longer need the column so the data can be deleted, but I suspect I can do that in the Power Query after I find out how to grab the dollar amounts. I've been trying to split things but it's becoming really messy;.

 

[alansidman]

You did not show us your expected results, so I am guessing at the requirements. Here is some Mcode to extract the $ amounts.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, "$", " "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text Between Delimiters", "Column1", Splitter.SplitTextByEachDelimiter({"<"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.2"})
in
    #"Removed Columns"