Forum Discussion

maasool's avatar
maasool
Helper I
3 years ago
Solved

Query info from web not in a table

Is there any way to query the latest stock price from this web page? https://www.gettex.de/en/gettex-die-schnelle-und-guenstige-boerse/?isin=IE00B4WXJH41&name=ishares-eur-govbd10-15yr-ueeur-d&token=4f37389707b01bef874ca4d47e99bf80

Circled with blue on the picture


It is not shown in any tables, when you query this page.

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi maasool ,

     

    You need to find the current value using the "Get from examples" option, then PQ writes the HTML location for you:

    let
    Source = Web.BrowserContents("https://www.gettex.de/en/gettex-die-schnelle-und-guenstige-boerse/?isin=IE00B4WXJH41&name=ishares-eur-govbd10-15yr-ueeur-d&token=4f37389707b01bef874ca4d47e99bf80"),
    extractPriceHTML = Html.Table(Source, {{"Column1", ".stock-details > DIV > :nth-child(2)"}}),
    addGetNumerical = Table.AddColumn(extractPriceHTML, "getNumerical", each Text.Select([Column1], List.Combine({{"0".."9"}, {","}}))),
    chgTypeWithLocale = Table.TransformColumnTypes(addGetNumerical, {{"getNumerical", type number}}, "de-DE")
in
    chgTypeWithLocale

     

    Example query output:

     

    I've done a bit of formatting to get the output into my regional format ("." instead of ","), but once you get the value into PQ, you can do whatever you want with it.

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi maasool ,

     

    You need to find the current value using the "Get from examples" option, then PQ writes the HTML location for you:

    let
    Source = Web.BrowserContents("https://www.gettex.de/en/gettex-die-schnelle-und-guenstige-boerse/?isin=IE00B4WXJH41&name=ishares-eur-govbd10-15yr-ueeur-d&token=4f37389707b01bef874ca4d47e99bf80"),
    extractPriceHTML = Html.Table(Source, {{"Column1", ".stock-details > DIV > :nth-child(2)"}}),
    addGetNumerical = Table.AddColumn(extractPriceHTML, "getNumerical", each Text.Select([Column1], List.Combine({{"0".."9"}, {","}}))),
    chgTypeWithLocale = Table.TransformColumnTypes(addGetNumerical, {{"getNumerical", type number}}, "de-DE")
in
    chgTypeWithLocale

     

    Example query output:

     

    I've done a bit of formatting to get the output into my regional format ("." instead of ","), but once you get the value into PQ, you can do whatever you want with it.

     

    Pete

    • maasool's avatar
      maasool
      Helper I
      3 years ago

      Impressive, got it working by using your code! From where is this "Get from examples" option accessible in power query? Also, did this thing wrote the code itself or did you write it?

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Power BI writes the HTML location itself once you identify the value you're looking for. I wrote the [getNumerical] column to format into a proper number.

         

        In the HTML browser (after you've pasted your URL into the Web connector), click here:

         

        You'll get a preview of the page and an editable table below. Use the preview to identify the value you want (as of now, 147,085):

         

        Double-click in the first cell of the table below (you can repeat in the cells below if you want more single values) and you'll see a list of values generated by PQ based on all the HTML locations in the page code. Choose the value you want and PQ will take the HTML location for that item and put it into the Html.Table() function for you:

         

         

         

        Hit the 'Transform Data' button to bring it into PQ and be able to transform/manipulate the value you've selected.

         

        Pete