Forum Discussion

msabrooks's avatar
msabrooks
Copper Contributor
Mar 08, 2019
Solved

Power Query and dynamic URL

Hi,   I have been beating my head against the wall for 2 weeks trying to figure out how to get the power query from web to work by updating a dynamic URL by data input from a cell.   Below is the...
BI & Data Analytics
Need Help - Dynamic URL from cell for Power Query from Web
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 08, 2019

    Hi Mike,

     

    Sorry, but I didn't catch what exactly doesn't work. Based on your code you have in your sheet table URL with the column named Value from first row of which you take your url as parameter.

     

    For only one cell which you use as parameter I'd not use table, just name that cell (let say as URL) and get parameter as

    ...    
    GetParameter = Excel.CurrentWorkbook(){[Name="URL"]}[Content],
    URL = GetParameter[Column1]{0},
...

    but in general that's only cosmetic

pqsingh's avatar
pqsingh
Copper Contributor
Jul 07, 2022
Hi, I am facing some problem with a link, it is a download link. Earlier I was able get data from the excel file under the link but now I am not this error 'Details: "External table is not in the expected format."' And I can not get any data. It would be helpful if anyone can give a workaround.
Lorenzo's avatar
Lorenzo
Silver Contributor
Jul 07, 2022

Hi pqsingh 

 

Your point isn't really related to this thread, it'd be better you raise a separate discussion, if necessary...

 

Let's be logic: "Earlier I was able get data from the excel file under the link but now I am not". If you didn't make any change to your query it's almost sure the problem doesn't come from the query but from the Data Source - the Excel find "behind" the link

 

Re. DataFormat.Error: External table is not in the expected format with Excel files there's a quite common reason that i.e. Maxim Zelensky explained https://social.technet.microsoft.com/Forums/en-US/df02e679-a65e-4d9c-90e5-05074770dfa7/dataformaterror-external-table-is-not-in-expected-format?forum=powerquery:

Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error. I met this errors permanently with my accounting system exports. The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files

Resources