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
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