Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rootsmusic
Helper I
Helper I

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.

rootsmusic_0-1660757997282.png

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.

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

1 REPLY 1
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors