Forum Discussion
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
- pdtcaskeyCopper Contributor
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.
- pranjitpuneCopper ContributorThanks , 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]"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