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 operat...
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., https://learn.microsoft.com/en-us/powerquery-m/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
    Diamond Contributor
    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