HTML into Two Meausures

joe100 · ‎03-11-2022

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:

<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>

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

lbendlin · ‎03-13-2022

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"