Forum Discussion

Hansdecocq's avatar
Hansdecocq
Frequent Visitor
4 years ago
Solved

How to extract table name from WEB import

Hey “POWER BI boy’s and girls” 😊
 
What is my help-question?
How can I extract the generated name (see content “Web.BrowserContents” returned by the first the power query step? 
 
What is the situation:
In power query I extract a table from this site eq.
(This the government side provided by Dutch Customs-department which collects the import tax-fees for (a Web shop) stuff sold outside the Netherlands and paid for by the customer in foreign currency).
  
This site provides a Table of exchange rate currencies factors to use (= the “wisselkoersen” in Dutch) for the current calendar (active) calendar-month which is (normally) nice and correct. Only if we connect in that active month of course. But this I not always the case. Sometimes (for whatever reason) we do need the exchange rate currencies factor for an earlier month from that specific tax-site.
 
How do I get the Data?
I Use Power Bi to collect the exchange currencies table using the standard “web-connector” and with the above link. 

Data connection through PowerBI

 
 
 
 
 
 
 
 
And if Power BI has performed “the trick” to collect the data, it returns the table. So far so good.
The Power BI query returns a “named table” like:
“Wisselkoers over de maand februari van het jaar 2022”.  
This name is obviously assigned to the Query property’s
Property window result table query
  • I must do several transformations to the received Table and I need this mentioned name in the property’s windows (shown above). (I have to add columns with the Calendar dates for the start-date and end-date for this rate table. 
  • I do like to have this process to run in “an unattended query”.  
     So, in this query I must be able to extract the name of the table (see property windows below) to extract the month name and year for generating columns for the start and end-dates per currencies. 
  •  I managed to make the content visible of the 
 
<?xml version="1.0" encoding="utf-8"?><Mashup xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/DataMashup"><Client>PBIDesktop</Client><Version>2.100.1401.0</Version><MinVersion>1.5.3296.0</MinVersion><Culture>nl-NL</Culture><SafeCombine>true</SafeCombine><Items><Query Name="Wisselkoers over de maand februari van het jaar 2022"><Formula><![CDATA[let Source = Web.BrowserContents("https://www.belastingdienst.nl/wps/wcm/connect/nl/douane_voor_bedrijven/content/hulpmiddel-wisselkoersen"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.table > * > TR > :nth-child(1)"}, {"Column2", "TABLE.table > * > TR > :nth-child(2)"}, {"Column3", "TABLE.table > * > TR > :nth-child(3)"}, {"Column4", "TABLE.table > * > TR > :nth-child(4)"}, {"Column5", "TABLE.table > * > TR > :nth-child(5)"}}, [RowSelector="TABLE.table > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Valuta", type text}, {"Muntcode", type text}, {"1 euro = vreemdevaluta", type number}, {"1 vreemdevaluta = euro", type number}, {"Wijziging per", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Muntcode", "1 euro = vreemdevaluta", "1 vreemdevaluta = euro", "Wijziging per", "Valuta"}) in #"Reordered Columns"]]></Formula><LoadToReport>false</LoadToReport><IsParameterQuery>false</IsParameterQuery><IsDirectQuery xsi:nil="true" /></Query></Items></Mashup>
 

TIA Hans

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    The name of the table comes from a caption tag buried in the Web.BrowserContents text.

     

    In this particular case, you can extract it like this:

    let
    Source = Web.BrowserContents("https://www.belastingdienst.nl/wps/wcm/connect/nl/douane_voor_bedrijven/content/hulpmiddel-wisselkoersen"),
    TableName = Text.BetweenDelimiters(Source, "<caption>", "</caption>")
in
    TableName

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    The name of the table comes from a caption tag buried in the Web.BrowserContents text.

     

    In this particular case, you can extract it like this:

    let
    Source = Web.BrowserContents("https://www.belastingdienst.nl/wps/wcm/connect/nl/douane_voor_bedrijven/content/hulpmiddel-wisselkoersen"),
    TableName = Text.BetweenDelimiters(Source, "<caption>", "</caption>")
in
    TableName
    • Hansdecocq's avatar
      Hansdecocq
      Frequent Visitor
      4 years ago

      Thanks for this (quick) reply. The first thing I'll do tomorrow is try this 🙂

      • Hansdecocq's avatar
        Hansdecocq
        Frequent Visitor
        4 years ago

        How do I reward you with Kudo's ????? ( This is new for me)

    • Hansdecocq's avatar
      Hansdecocq
      Frequent Visitor
      4 years ago

      Hey Alexis

      I couldn't wait (:)) to try it tomorrow, so I did it 2 minutes ago.

      1. I created a new query with the data provided by you
      2. and...........Yep "TableName" has the (same) name like the main query.

      (now I have to figure out the rest of the procedure, like extracting "month name and year". Thereafter I need to add two columns one with the constructed StartDateRates and the other with the EndDateRates.)
       
      TOP!!!! Thank you for your help.