Excel Power Query/M - 根据网站上可用的页数动态组合连接数

Question

我才刚刚开始涉足 M 的世界(我买了一本书和所有的东西!)，所以我希望你们中的一个聪明人可以帮助我 :)

我使用 Power Query 从网站上跨多个页面的表中提取数据

幸运的是，他们的 URL 以 “page=1” 结尾， ETC。

我已将每个页面加载为单独的连接(仅限连接)，并有一个将所有页面附加在一起的主表。每个连接被命名为 “Page 1” , “Page 2” ， ETC。

原始代码是:
let
Source = Table.Combine({#"Page 1", #"Page 2", #"Page 3", #"Page 4", #"Page 5", #"Page 6", #"Page 7", #"Page 8", #"Page 9", #"Page 10", #"Page 11", #"Page 12", #"Page 13"})
in
Source
有数据的页面数量不时变化，我想找出一种方法来动态加载最多可用页面数量

(事实证明，如果您输入的页码高于最大页数，该网站会重复最后一页，所以它会在我的主表中复制它们，因为我正在加载 30 页)

因此，在只有 8 页数据的示例中，我希望它加载:
let
Source = Table.Combine({#"Page 1", #"Page 2", #"Page 3", #"Page 4", #"Page 5", #"Page 6", #"Page 7", #"Page 8"})
in
Source
再次幸运，“Page 1 of 8”在网站上有自己的一行，所以我可以很容易地用 Power Query 解析出“8”

快速搜索 this solution

这似乎是一个不错的计划!
我可以学习一些M!

但我不想使用单独的文件，所以搜索更多我发现了一个不同的解决方案，包括使用 Excel.CurrentWorkbook(){[Name="TabelName"]}[Content]
所以我把它和上面的结合起来得到:
Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content],
CombineTable = Table.Combine(Source[Column1])
in
CombineTable
我制作了一张名为 TableName 的表格与 ‘Page 1’通过‘Page 30’向下，用 IF 包围它声明，因此如果它大于页数，则该单元格中将为空白

把它扔进高级编辑器
它看到了‘Page 1’ ，但不能用它做一张 table

我有一种更简单的方法可以做到这一点，但我的 Google-Fu 在这里让我失望了。看来这应该是一个非常简单的解决方案哈哈

如果有人能指出一个优雅的解决方案的方向，我将永远感激不尽:)

-编辑-
每个连接的代码(该 URL 是一个内部业务 URL，所以我刚刚将其替换为 URL ):
let
Source = Web.Page(Web.Contents("https://URL&page=1")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text}, {"Policy number", type text}, {"Creation date and time", type text}, {"Deadline", type text}, {"Case Ref", type text}, {"Lock User", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",2),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"Creation date and time", type datetime}, {"Deadline", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{""})
in
#"Removed Columns"
这是获取页码的代码:
let
Source = Web.Page(Web.Contents("https:URL&page=1")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text}, {"Policy number", type text}, {"Creation date and time", type text}, {"Deadline", type text}, {"Case Ref", type text}, {"Lock User", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",21),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Creation date and time"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Creation date and time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Creation date and time.1", "Creation date and time.2", "Creation date and time.3", "Creation date and time.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Creation date and time.1", type text}, {"Creation date and time.2", Int64.Type}, {"Creation date and time.3", type text}, {"Creation date and time.4", Int64.Type}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type1",{"Creation date and time.4"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Creation date and time.4", "I&W - Retail - Pages"}})
in
#"Renamed Columns"

Answer 1

如果不访问您的实际 URL，很难对此进行测试。

但是我们可以通过生成页面列表，使用函数查询每个页面以返回我们想要的数据，然后组合这些数据，将所有内容合并到一个查询中。

let
    SourceURL = "https://URL",

    LastPageNumber = (MyURL) =>
        let
            Source = Web.Page(Web.Contents(MyURL & "&page=1")),
            WebData = Source{0}[Data],
            #"Filtered Page Text" = Table.SelectRows(WebData, each Text.StartsWith([Creation date and time], "Page ")),
            #"Last Page Number" = try Number.FromText(Text.AfterDelimiter(#"Filtered Page Text"{0}[Creation date and time], " ", {0, RelativePosition.FromEnd})) otherwise 8
        in
            #"Last Page Number",

    PageNumbers = List.Numbers(1, LastPageNumber(SourceURL)),
    #"Table from List" = Table.FromList(List.Transform(PageNumbers, each Number.ToText(_)), Splitter.SplitByNothing(), {"Page"}),

    GetWebData = (MyURL, PageNumber) =>
        let
            Source = Web.Page(Web.Contents(MyURL & "&page=" & PageNumber)),
            WebData = Source{0}[Data],
            #"Removed Bottom Rows" = Table.RemoveLastN(WebData,2),
            #"Changed Type" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"", type text}, {"Policy number", type text}, {"Creation date and time", type datetime}, {"Deadline", type datetime}, {"Case Ref", type text}, {"Lock User", type text}})
        in
            #"Changed Type",

    #"Retrieve Web Data" = Table.AddColumn(#"Table from List", "WebData", each try GetWebData(SourceURL, [Page]) otherwise #table({""},{})),
    #"Combined Web Data" = Table.Combine(#"Retrieve Web Data"[WebData])
in
    #"Combined Web Data"

更改 SourceURL适当的值。

第一个函数 LastPageNumber过滤 Creation date and time以“Page”开头的值列，并在最后一个空格之后提取值 - 这为我们提供了“Page 1 of 8”中的 8。

现在我们可以生成一个从 1 到最后一个页码的数字列表。然后是第二个函数 GetWebData检索每个指定页码的 Web 数据，并将返回的这些表放入一列。

最后，我们结合使用 Table.Combine 返回的 web 数据表。