时间戳

首页 > 解决方案 > 电源查询 Excel 循环日期

excel - 电源查询 Excel 循环日期

问题描述

我想直接在 excel powerquery 中循环这个查询。我想循环链接中找到的日期：从链接中的日期到今天的日期。数据必须全部插入到查询中。我该如何解决？谢谢你。

let
    Origine = Web.Page(Web.Contents("https://www.forebet.com/scripts/getrs.php?ln=it&tp=bts&in=2019-06-01&ord=0")),
    Data0 = Origine{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{1}[Children],
    #"Rimosse colonne" = Table.RemoveColumns(Children1,{"Kind", "Name", "Children"}),
    #"Analizzato JSON" = Table.TransformColumns(#"Rimosse colonne",{},Json.Document),
    Text = #"Analizzato JSON"{0}[Text],
    #"Conversione in tabella" = Table.FromList(Text, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Tabella Column1 espansa" = Table.ExpandRecordColumn(#"Conversione in tabella", "Column1", {"id", "host_id", "guest_id", "league_id", "Round", "Host_SC", "Guest_SC", "DATE", "DATE_BAH", "Host_SC_HT", "Guest_SC_HT", "comment", "match_preview", "host_stadium", "match_stadium", "HOST_NAME", "GUEST_NAME", "penalty_score", "extra_time_score", "goalsavg", "host_sc_pr", "guest_sc_pr", "weather_low", "weather_high", "weather_code", "short_tag", "Pred_gg", "Pred_no_gg", "odds_gg", "odds_gg_frac", "odds_gg_y", "odds_gg_n", "odds_gg_y_frac", "odds_gg_n_frac"}, {"Column1.id", "Column1.host_id", "Column1.guest_id", "Column1.league_id", "Column1.Round", "Column1.Host_SC", "Column1.Guest_SC", "Column1.DATE", "Column1.DATE_BAH", "Column1.Host_SC_HT", "Column1.Guest_SC_HT", "Column1.comment", "Column1.match_preview", "Column1.host_stadium", "Column1.match_stadium", "Column1.HOST_NAME", "Column1.GUEST_NAME", "Column1.penalty_score", "Column1.extra_time_score", "Column1.goalsavg", "Column1.host_sc_pr", "Column1.guest_sc_pr", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.short_tag", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Riordinate colonne" = Table.ReorderColumns(#"Tabella Column1 espansa",{"Column1.Round", "Column1.id", "Column1.host_id", "Column1.guest_id", "Column1.league_id", "Column1.Host_SC", "Column1.Guest_SC", "Column1.DATE", "Column1.DATE_BAH", "Column1.Host_SC_HT", "Column1.Guest_SC_HT", "Column1.comment", "Column1.match_preview", "Column1.host_stadium", "Column1.match_stadium", "Column1.HOST_NAME", "Column1.GUEST_NAME", "Column1.penalty_score", "Column1.extra_time_score", "Column1.goalsavg", "Column1.host_sc_pr", "Column1.guest_sc_pr", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.short_tag", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Rimosse colonne1" = Table.RemoveColumns(#"Riordinate colonne",{"Column1.id", "Column1.host_id", "Column1.guest_id", "Column1.league_id"}),
    #"Merge di colonne" = Table.CombineColumns(#"Rimosse colonne1",{"Column1.Host_SC", "Column1.Guest_SC"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Sottoposto a merge"),
    #"Merge di colonne1" = Table.CombineColumns(#"Merge di colonne",{"Column1.Host_SC_HT", "Column1.Guest_SC_HT"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Sottoposto a merge.1"),
    #"Riordinate colonne1" = Table.ReorderColumns(#"Merge di colonne1",{"Column1.Round", "Column1.comment", "Sottoposto a merge", "Column1.DATE", "Column1.DATE_BAH", "Sottoposto a merge.1", "Column1.match_preview", "Column1.host_stadium", "Column1.match_stadium", "Column1.HOST_NAME", "Column1.GUEST_NAME", "Column1.penalty_score", "Column1.extra_time_score", "Column1.goalsavg", "Column1.host_sc_pr", "Column1.guest_sc_pr", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.short_tag", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Rimosse colonne2" = Table.RemoveColumns(#"Riordinate colonne1",{"Column1.DATE"}),
    #"Riordinate colonne2" = Table.ReorderColumns(#"Rimosse colonne2",{"Column1.Round", "Column1.comment", "Column1.DATE_BAH", "Sottoposto a merge.1", "Sottoposto a merge", "Column1.match_preview", "Column1.host_stadium", "Column1.match_stadium", "Column1.HOST_NAME", "Column1.GUEST_NAME", "Column1.penalty_score", "Column1.extra_time_score", "Column1.goalsavg", "Column1.host_sc_pr", "Column1.guest_sc_pr", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.short_tag", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Rimosse colonne3" = Table.RemoveColumns(#"Riordinate colonne2",{"Column1.match_preview", "Column1.host_stadium", "Column1.match_stadium"}),
    #"Riordinate colonne3" = Table.ReorderColumns(#"Rimosse colonne3",{"Column1.Round", "Column1.comment", "Column1.DATE_BAH", "Column1.HOST_NAME", "Sottoposto a merge.1", "Sottoposto a merge", "Column1.GUEST_NAME", "Column1.penalty_score", "Column1.extra_time_score", "Column1.goalsavg", "Column1.host_sc_pr", "Column1.guest_sc_pr", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.short_tag", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Rimosse colonne4" = Table.RemoveColumns(#"Riordinate colonne3",{"Column1.penalty_score", "Column1.extra_time_score"}),
    #"Riordinate colonne4" = Table.ReorderColumns(#"Rimosse colonne4",{"Column1.Round", "Column1.comment", "Column1.DATE_BAH", "Column1.short_tag", "Column1.HOST_NAME", "Sottoposto a merge.1", "Sottoposto a merge", "Column1.GUEST_NAME", "Column1.goalsavg", "Column1.host_sc_pr", "Column1.guest_sc_pr", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Riordinate colonne4", "Column1.DATE_BAH", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.DATE_BAH.1", "Column1.DATE_BAH.2"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"Column1.DATE_BAH.1", type date}, {"Column1.DATE_BAH.2", type time}}),
    #"Riordinate colonne5" = Table.ReorderColumns(#"Modificato tipo",{"Column1.Round", "Column1.comment", "Column1.DATE_BAH.1", "Column1.DATE_BAH.2", "Column1.short_tag", "Column1.HOST_NAME", "Sottoposto a merge.1", "Sottoposto a merge", "Column1.GUEST_NAME", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.goalsavg", "Column1.host_sc_pr", "Column1.guest_sc_pr", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Rimosse colonne5" = Table.RemoveColumns(#"Riordinate colonne5",{"Column1.host_sc_pr", "Column1.guest_sc_pr"}),
    #"Riordinate colonne6" = Table.ReorderColumns(#"Rimosse colonne5",{"Column1.Round", "Column1.comment", "Column1.DATE_BAH.1", "Column1.DATE_BAH.2", "Column1.short_tag", "Column1.HOST_NAME", "Sottoposto a merge.1", "Sottoposto a merge", "Column1.GUEST_NAME", "Column1.Pred_gg", "Column1.Pred_no_gg", "Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.goalsavg", "Column1.weather_low", "Column1.weather_high", "Column1.weather_code", "Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Rimosse colonne6" = Table.RemoveColumns(#"Riordinate colonne6",{"Column1.odds_gg", "Column1.odds_gg_frac", "Column1.odds_gg_y_frac", "Column1.odds_gg_n_frac"}),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Rimosse colonne6",{{"Column1.weather_low", Int64.Type}, {"Column1.weather_high", Int64.Type}, {"Column1.weather_code", Int64.Type}}),
    #"Sostituito valore" = Table.ReplaceValue(#"Modificato tipo1",".",",",Replacer.ReplaceText,{"Column1.odds_gg_y", "Column1.odds_gg_n", "Column1.goalsavg"}),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Sostituito valore",{{"Column1.odds_gg_y", type number}, {"Column1.odds_gg_n", type number}, {"Column1.goalsavg", type number}, {"Column1.Pred_gg", Percentage.Type}, {"Column1.Pred_no_gg", Percentage.Type}}),
    #"Divisa colonna" = Table.TransformColumns(#"Modificato tipo2", {{"Column1.Pred_gg", each _ / 100, Percentage.Type}}),
    #"Divisa colonna1" = Table.TransformColumns(#"Divisa colonna", {{"Column1.Pred_no_gg", each _ / 100, Percentage.Type}})
in
    #"Divisa colonna1"

标签： excelpowerquery

解决方案

下面的代码应该可以让您获取给定日期范围的数据。

let
    // Should return JSON for a given date.
    getJsonForDate = (someDate as date) as text => 
        let
            httpResponse = Web.Contents(
                "https://www.forebet.com/scripts/getrs.php",
                [Query = [ln = "it", tp = "bts", in = Date.ToText(someDate, "YYYY-MM-DD"), order = "0"]]
            ),
            htmlParsed = Web.Page(httpResponse),
            extracted = htmlParsed{0}[Data]{0}[Children]{1}[Children]{0}[Text] // Will throw an error if path changes
        in extracted,
    // Should return JSON for a given date range.
    getJsonForDateRange = (inclusiveStart as date, inclusiveEnd as date) as table =>
        let
            dates = List.Dates(inclusiveStart, Duration.TotalDays(inclusiveEnd - inclusiveStart) + 1, #duration(1, 0, 0, 0)),
            jsonForDates = List.Transform(dates, each [date = _, json = getJsonForDate(_)]),
            toTable = Table.FromRecords(jsonForDates, type table [date = date, json = text])
        in toTable,
    // Should return a table representing the JSON passed in.
    parseJsonResponse = (someJson as text) as table => 
        let
            parsed = Json.Document(someJson),
            toTable = if parsed <> null then Table.FromRecords(parsed) else #table({}, {})
        in toTable,
    
    data = getJsonForDateRange(#date(2020, 8, 30), DateTime.Date(DateTime.LocalNow())),
    parsed = Table.TransformColumns(data, {"json", parseJsonResponse, type table}),
    expanded = Table.ExpandTableColumn(parsed, "json", {"Round", "Host_SC", "Guest_SC", "DATE_BAH", "Host_SC_HT", "Guest_SC_HT", "comment", "HOST_NAME", "GUEST_NAME", "goalsavg", "weather_low", "weather_high", "weather_code", "short_tag", "Pred_gg", "Pred_no_gg", "odds_gg_frac", "odds_gg_y", "odds_gg_n"}),
    combineColumns = Table.CombineColumns(expanded, {"Host_SC", "Guest_SC"}, Combiner.CombineTextByDelimiter(":", QuoteStyle.None), "Sottoposto a merge"),
    combineColumnsAgain = Table.CombineColumns(combineColumns, {"Host_SC_HT", "Guest_SC_HT"}, Combiner.CombineTextByDelimiter(":", QuoteStyle.None), "Sottoposto a merge.1"),
    transformColumns = Table.TransformColumns(combineColumnsAgain, {
        {"DATE_BAH", each Date.FromText(Text.BeforeDelimiter(_, " ")), type date},
        {"weather_low", Number.From, Int64.Type},
        {"weather_high", Number.From, Int64.Type},
        {"weather_code", Number.From, Int64.Type},
        {"odds_gg_y", each Number.FromText(_, "en-US"), type number},
        {"odds_gg_n", each Number.FromText(_, "en-US"), type number},
        {"goalsavg", each Number.FromText(_, "en-US"), type number},
        {"Pred_gg", each Number.From(_, "en-US") / 100, Percentage.Type},
        {"Pred_no_gg", each Number.From(_, "en-US") / 100, Percentage.Type}
    })
in
    transformColumns

您可以通过将代码中的这一行更改为您需要的任何日期范围来更改日期范围。

data = getJsonForDateRange(#date(2020, 8, 30), DateTime.Date(DateTime.LocalNow())),
  • 最好先检查网站 ( www.forebet.com) 是否在其服务/使用条款中禁止抓取/机器人。
  • 目前，每个请求之间没有延迟。因此，如果您指定较大的日期范围，您可能会在短时间内发送大量请求，这可能会对他们的服务器产生不利影响。
  • 根据您的问题，该代码假定服务器的响应始终是具有特定结构的 HTML 文档。如果服务器（无论出于何种原因）返回具有不同结构的响应，则此代码将引发错误。

推荐阅读