Forum Discussion

pranjitpune's avatar
pranjitpune
Copper Contributor
Aug 04, 2024

POWER QUERY EDITOR ISSUE IN CREATING DYNAMIC URL FROM A WEBSITE URL IN EXCEL SPREADSHEET

Hi Members, 

I am facing an issue in creating a dynamic URL to fetch market data for Indian stocks based on two variables "COMPANY" and "QUARTER" as below 

"Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=https://www.bseindia.com/corporates/results.aspx?Code=
Right=540776"

The advance editor code for Table 5 in power query for excel is as below- 

let
Source = Web.BrowserContents("https://www.bseindia.com/corporates/results.aspx?Code="& COMPANY() &"&qtr="& QUARTER() &""),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2)"}, {"Column2", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(1), TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}, {"Column3", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}, {"Column4", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}, {"Column5", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}}, [RowSelector="TABLE.tablesorter > * > TR"])
in
#"Extracted Table From Html"

The advance editor code for Query COMPANY in power query for excel is as below- 

let COMPANY = () =>
let
Source = Excel.CurrentWorkbook(){[Name="COMPANY"]}[Content],
Column1 = Source{0}[Column1]
in
Column1
in
COMPANY

The advance editor code for Query QUARTER in power query for excel is as below- 

let QUARTER = () =>
let
Source = Excel.CurrentWorkbook(){[Name="QUARTER"]}[Content],
Column1 = Source{0}[Column1]
in
Column1
in
QUARTER

NOTE: original URL from the website from which we retrieve Table 5 is below:

https://www.bseindia.com/corporates/results.aspx?Code=500325&qtr=105.00

Where 500325 is a scrip/Company code for particular stock/company in number format and

105.00 is a Quarter Code for that company for a particular quarter for a financial year. The quarter code is independent of stocks and based on year and quarter combination. Our target is to replace the quarter and company code in URL with the Variables as mentioned above to fetch data for various companies quarterly results from various quarters. 

 

Please suggest a workaround. 

 

Thanks in advance.

 

Regards, 

 

Pranjit Parasar

 

Excel
  • pdtcaskey's avatar
    pdtcaskey
    Copper Contributor
    Aug 05, 2024

    pranjitpune 

    I haven't sorted through your code, but the error message indicates that you need to convert your values for [Quarter] into text (e.g., Text.From)  before concatenating with [Company]; PQ does not automatically convert types like Excel does.

     

    Maybe something like

     

    Column1 = List.Transform(Source{0}[Column1], each Text.From(_))

    There are other conversions that can format date data into any textual format you need.

     

    • pranjitpune's avatar
      pranjitpune
      Copper Contributor
      Aug 05, 2024
      Thanks , Once I added the above code to both COMPANY AND QUARTER QUERIES ; The earlier error disappeared but below new error came
      "An error occurred in the ‘COMPANY’ query. Expression.Error: We cannot convert the value 500009 to type List.
      Details:
      Value=500009
      Type=[Type]"
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Aug 05, 2024

        pranjitpune 

        Be sure both parameters are texts

        when

        let

    pCode = Excel.CurrentWorkbook(){[Name="Code"]}[Content]{0}[Column1],
    pQtr = Excel.CurrentWorkbook(){[Name="Qtr"]}[Content]{0}[Column1],
    pSite = "https://www.bseindia.com/corporates/results.aspx",
    pURL = pSite 
        & "?"
        & "Code=" & Text.From(pCode)
        & "&"
        & "qtr=" & pQtr
        ,

    Source = Web.BrowserContents(pURL),
    TableFromHtml = Html.Table(
        Source,
        {
              {"Column1", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2)"}
            , {"Column2", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) +" &
               "TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(1)," &
               "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) +" &
               "TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}
            , {"Column3", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) +" &
               "TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}
            , {"Column4", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) +" &
               "TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}
            , {"Column5", "TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) +" &
               "TD[colspan=""4""]:not([rowspan]):nth-child(2):nth-last-child(1)"}
        },
        [RowSelector="TABLE.tablesorter > * > TR"])
in
    TableFromHtml

        Please see attached

Resources