Forum Discussion
Web.Contents vs. Web.BrowserContents
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.
Hi Anonymous -
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 Custom1However 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"
1 Reply
Hi Anonymous -
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 Custom1However 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"