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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors