I have never used Power Query before so I'm not sure if what I'm asking is even possible. I want to find out a way in excel to dynamically import data from a website based on the value given in another cell, something like importhtml in google sheets. For example, here's what I created using google sheets: 
我以前从未使用过Power Query，所以我不确定我的问题是否可能。我想在excel中找到一种方法，根据另一个单元格中给定的值动态地从网站导入数据，类似于谷歌表中的importhtml。例如，这是我使用谷歌表创建的:
If you notice,
如果你注意到,
- the first column is an
NPInumber which is what I'd provide (copy and paste to the column in excel). - 第一列是一个NPI数字，这就是我要提供的(复制并粘贴到excel中的列)。
- the second column is the
urlfrom where the data to is to be downloaded. The url number value is referenced to column A. - 第二列是要下载数据的url。url编号值引用到列A。
- importHTML then imports the data and puts them in the column following URL.
- importHTML然后导入数据并将它们放在URL后面的列中。
So, is it possible to do something like this using Excel Power Query?
那么，是否可以使用Excel Power Query执行类似的操作?
Although I used the regular url to pull down the data in html table format, I prefer to use JSON if possible. The only reason I didn't use JSON in google sheets is because I don't know how to. The URL for NPI lookup API is https://npiregistry.cms.hhs.gov/api?number= where we can enter any NPI number to lookup.
虽然我使用了常规的url以html表格格式拉下数据，但如果可能的话，我更喜欢使用JSON。我没有在谷歌表中使用JSON的唯一原因是我不知道如何使用。NPI查找API的URL是https://npiregistry.cms hhs.gov/api?number=我们可以输入任何要查找的NPI编号。
1 个解决方案
#1
1
Yes, Power Query's Web.Contents function has a parameter for the URL - this can be an expression or a literal, or a concatenation of both. You can get a jump start on the syntax by using the "From Web" button (in Excel 2016+, this is (sadly) a bit buried under Data / New Query / From Other Sources) - this will generate the code including the Web.Contents function with a literal URL. Power Query will also look at the contents returned and wrap the appropriate function around Web.Contents e.g. Json.Document.
是的,权力查询的网页。Contents函数有URL的参数——可以是表达式或文字，也可以是两者的串联。您可以通过使用“From Web”按钮(在excel2016 +中，这(令人遗憾的)有点隐藏在数据/新查询/其他来源下)来启动语法——这将生成包含Web的代码。content函数具有文字URL。Power Query还会查看返回的内容，并在Web周围包装适当的功能。例如Json.Document内容。
You could start from your existing Google Sheets file (they are also easy to consume using Get Data / From Web), and use Add Custom Column to paste in your Web.Contents code. You can insert a column reference to make the URL dynamic - using & for string concatenation.
您可以从现有的谷歌表文件开始(使用Get Data / from Web也很容易使用它们)，并使用Add Custom列在Web中粘贴。内容的代码。您可以插入一个列引用来使URL动态使用&用于字符串连接。
#1
1
Yes, Power Query's Web.Contents function has a parameter for the URL - this can be an expression or a literal, or a concatenation of both. You can get a jump start on the syntax by using the "From Web" button (in Excel 2016+, this is (sadly) a bit buried under Data / New Query / From Other Sources) - this will generate the code including the Web.Contents function with a literal URL. Power Query will also look at the contents returned and wrap the appropriate function around Web.Contents e.g. Json.Document.
是的,权力查询的网页。Contents函数有URL的参数——可以是表达式或文字，也可以是两者的串联。您可以通过使用“From Web”按钮(在excel2016 +中，这(令人遗憾的)有点隐藏在数据/新查询/其他来源下)来启动语法——这将生成包含Web的代码。content函数具有文字URL。Power Query还会查看返回的内容，并在Web周围包装适当的功能。例如Json.Document内容。
You could start from your existing Google Sheets file (they are also easy to consume using Get Data / From Web), and use Add Custom Column to paste in your Web.Contents code. You can insert a column reference to make the URL dynamic - using & for string concatenation.
您可以从现有的谷歌表文件开始(使用Get Data / from Web也很容易使用它们)，并使用Add Custom列在Web中粘贴。内容的代码。您可以插入一个列引用来使URL动态使用&用于字符串连接。