HTML into Two Meausures

joe100 — Sat, 12 Mar 2022 07:41:16 GMT

Dear Friends!

Your assistance will much appreaciated,

I have a column where each cell is a table with different number of rows and quantities. The cell is presented in HTML format.

I wish to extract two measurements:

Number of Rows (w/o header) ==> in this case 2
Total QTY ==> in this case 13000

Can you please help with a visual of function?

Thank you

Re: HTML into Two Meausures

lbendlin — Mon, 14 Mar 2022 00:10:53 GMT

You can't really do that in DAX unless you want to do some serious string gymnastics. However this is rather easy to achieve in Power Query as it has an XML parser.

Note that your HTML is suspicious. instead of <th> it uses <td><strong> . Not cool.

let Source = "<table border=""1"" cellpadding=""1"" cellspacing=""1"" style=""width:500px""><tbody><tr><td><strong>CODE</strong></td><td><strong>DC</strong></td><td><strong>TDM</strong></td><td><strong>QTY</strong></td><td><strong>TYPE</strong></td></tr><tr><td>118EM1</td><td>1234</td><td>AAAA</td><td>5000</td><td>CCCC</td></tr><tr><td>114EM1</td><td>1432</td><td>BBBB</td><td>8000</td><td>DDDD</td></tr></tbody></table>", #"Parsed XML" = Xml.Tables(Source), #"Changed Type" = Table.TransformColumnTypes(#"Parsed XML",{{"Attribute:border", Int64.Type}, {"Attribute:cellpadding", Int64.Type}, {"Attribute:cellspacing", Int64.Type}, {"Attribute:style", type text}}), tbody = #"Changed Type"{0}[tbody], tr = tbody{0}[tr], #"Expanded td" = Table.ExpandTableColumn(tr, "td", {"strong", "Element:Text"}, {"strong", "Element:Text"}), #"Added Index" = Table.AddIndexColumn(#"Expanded td", "Index", 0, 1, Int64.Type), #"Replaced Value" = Table.ReplaceValue(#"Added Index",null,each #"Added Index"[strong]{Number.Mod([Index],5)},Replacer.ReplaceValue,{"strong"}), #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([#"Element:Text"] <> null)), #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [Index],each Number.RoundDown([Index]/5),Replacer.ReplaceValue,{"Index"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[strong]), "strong", "Element:Text"), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"QTY", Int64.Type}}) in #"Changed Type1"

topic Re: HTML into Two Meausures in Developer

HTML into Two Meausures

Re: HTML into Two Meausures

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".