Solved: Web.Contents vs. Web.BrowserContents

rootsmusic · ‎08-17-2022

Since Excel doesn't have Web.BrowserContents, I'm trying to substitute Web.Contents. My source is essentially a text file with some repeating HTML tags. My M code using Web.BrowserContents is:

let
    Source = Web.BrowserContents(
        "https://raw.githubusercontent.com/openelections/openelections-sources-pa/master/2018/general/Forest_PA_2018_general.html"),
    #"Extracted Table From Html" = 
        Html.Table(Source, {{"Column1", "BODY"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Table From Html", {{"Column1", Splitter.SplitTextByDelimiter("<HTML>", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","#(lf)","|",Replacer.ReplaceText,{"Column1"})
in
    #"Replaced Value"

The output of these transformations is screenshoted below.

Substituting with Web.Contents, my M code becomes:

let
    Source = Web.Page(Web.Contents("https://raw.githubusercontent.com/openelections/openelections-sources-pa/master/2018/general/Forest_PA_2018_general.html")),
    Data0 = Source{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{1}[Children],
    Children2 = Children1{0}[Children],
    #"Removed Other Columns" = Table.SelectColumns(Children2,{"Text"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Other Columns", {{"Text", Splitter.SplitTextByDelimiter("<HTML>", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Text", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","#(lf)","|",Replacer.ReplaceText,{"Text"})
in
    #"Replaced Value"

Although there aren't any errors, the last step doesn't replace anything and I don't understand why. Should I modify the M code that uses Web.Contents? Thanks.

Daryl-Lynch-Bzy · ‎08-18-2022

Hi @rootsmusic -

Using a Power BI Dataflow will be easier because you can use the following M Code:

let
    Source = Web.Contents("https://raw.githubusercontent.com/openelections/openelections-sources-pa/master/2018/general/Forest_PA_2018_general.html"),
    #"Text From Binary" = Text.FromBinary( Source ),
    Custom1 = Html.Table( #"Text From Binary" ,  {{"Table", "PRE" }} )
in
    Custom1

However if you are stuck with Excel Power Query only, the following will work:

let
    Source = Web.Contents("https://raw.githubusercontent.com/openelections/openelections-sources-pa/master/2018/general/Forest_PA_2018_general.html"),
    #"Text From Binary" = Text.FromBinary( Source ),
    #"Converted to Table" = #table(1, {{#"Text From Binary"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Converted to Table", {{"Column1", Splitter.SplitTextByDelimiter("<PRE>", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Column1] <> "#(lf)<HTML>#(cr)#(lf)"),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","</HTML>","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","</PRE>","",Replacer.ReplaceText,{"Column1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Column1", Text.Trim, type text}})
in
    #"Trimmed Text"

View solution in original post

Daryl-Lynch-Bzy · ‎08-18-2022

Hi @rootsmusic -

Using a Power BI Dataflow will be easier because you can use the following M Code:

let
    Source = Web.Contents("https://raw.githubusercontent.com/openelections/openelections-sources-pa/master/2018/general/Forest_PA_2018_general.html"),
    #"Text From Binary" = Text.FromBinary( Source ),
    Custom1 = Html.Table( #"Text From Binary" ,  {{"Table", "PRE" }} )
in
    Custom1

However if you are stuck with Excel Power Query only, the following will work:

let
    Source = Web.Contents("https://raw.githubusercontent.com/openelections/openelections-sources-pa/master/2018/general/Forest_PA_2018_general.html"),
    #"Text From Binary" = Text.FromBinary( Source ),
    #"Converted to Table" = #table(1, {{#"Text From Binary"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Converted to Table", {{"Column1", Splitter.SplitTextByDelimiter("<PRE>", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Column1] <> "#(lf)<HTML>#(cr)#(lf)"),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","</HTML>","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","</PRE>","",Replacer.ReplaceText,{"Column1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Column1", Text.Trim, type text}})
in
    #"Trimmed Text"