Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to Solution.
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
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"
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
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
12 | |
12 | |
12 |