如何从1号单元（链接粘贴在单元格A1）中的功率查询从表格1到表格2提取数据？ [英] How to pull out data using power query from 1 cell (link pasted in cell A1) from sheet 1 to Sheet 2?

问题描述

我有一个网站的链接，从那里我想通过电源查询提取数据。每次这个链接都会根据需要改变。所以我需要每次使用电源查询。有没有办法建立电源查询，显示我会更改链接和数据将刷新？是否可能粘贴表格1的单元格A1中的链接，并将数据提取到表格2？请指导我。

I have a link of a website from where I want to pull out data through power query. Every time, this link will get changed as per need. So I need to use power query every time. Is there any way to build up power query which shows that I'll change link and data will be refreshed? Is it possible that I paste link in cell A1 of sheet 1 and I fetch data to sheet 2? Please guide me.

推荐答案

点击具有URL的单元格，然后打开菜单功能查询>Excel数据>从表格（取消选中我的表格标题）

Click on the cell with the URL, then open menu Power Query > Excel Data > From Table (uncheck "My table has headers")

在Power Query编辑器中，右键单击表的URL单元格，然后向下钻取以获取文本值。

In the Power Query editor, right click on the URL cell of the table and "drill down" to get the text value.

现在您需要做一些手册M代码转换，将此变量视为数据源。转到视图>显示公式栏，然后单击 fx 按钮添加自定义步骤。添加数据源调用，例如对于网页：

Now you need to do some manual M code transformation to treat this variable as a datasource. Go to View > Show formula bar, then click the fx button to add a custom step. Add the datasource call, e.g. for web pages:

= Web.Page(Web.Contents(Column1))

您可能需要回答有关数据隐私的一些问题（毕竟，您正在从Excel工作簿复制变量并将其发送到互联网！ ）

You might need to answer some questions about data privacy (after all, you're copying a variable from your Excel workbook and sending it to the internet!).

您可以编辑表格1中的链接，当您刷新查询时，您将看到表格2已更改。

You can edit the link in sheet 1, and when you refresh your query you'll see sheet 2 is changed.

编辑：

您的完整查询应如下所示：

Your full query should look like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Column1 = #"Changed Type"{0}[Column1],
    Custom1 = Web.Page(Web.Contents(Column1))
in
    Custom1

这篇关于如何从1号单元（链接粘贴在单元格A1）中的功率查询从表格1到表格2提取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！