let // Функция, которая будет циклически выполняться
getStat = (campaignId) => let
Source = Json.Document(Web.Contents("http://api.unisender.com/ru/api/getCampaignCommonStats?format=
json&api_key=5tmjbcir5g4xuew573mxcoh4ypo98xbbbadqgs4a&campaign_id=" & campaignId)),
result = Source[result],
#"Converted to Table" = Record.ToTable(result),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
in
#"Promoted Headers",
Source = Json.Document(Web.Contents("http://api.unisender.com/ru/api/getCampaigns?format=
json&api_key=5tmjbcir5g4xuew573mxcoh4ypo98xbbbadqgs4a")), result = Source[result],
#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
{"id", "start_time", "status", "message_id", "list_id", "subject", "sender_name", "sender_email",
"stats_url"},
{"id", "start_time", "status", "message_id", "list_id", "subject", "sender_name", "sender_email", "stats_url"}),
Campaigns = Table.TransformColumnTypes(#"Expanded Column1",{{"id", type text},
{"start_time", type datetime}, {"status", type text}, {"message_id", Int64.Type},
{"list_id", Int64.Type}, {"subject", type text}, {"sender_name", type text},
{"sender_email", type text}, {"stats_url", type text}}),
campaignsWithStat = Table.AddColumn(Campaigns, "stat", each getStat([id])),
#"Expanded stat" = Table.ExpandTableColumn(campaignsWithStat, "stat",
{"total", "sent", "delivered", "read_unique", "read_all", "clicked_unique", "clicked_all", "unsubscribed", "spam"},
{"total", "sent", "delivered", "read_unique", "read_all", "clicked_unique", "clicked_all", "unsubscribed", "spam"})
in
#"Expanded stat"