· 6 years ago · Mar 12, 2019, 11:42 PM
1let
2 Url = "https://xx.com/magento-api/rest/customers?order=entity_id&dir=asc",
3 Limit = "100",
4 consumerKey = "xx",
5 Token = "xx",
6 SignatureMethod = "PLAINTEXT",
7 Signature = "xxx",
8 TimeStamp = "1551909334",
9 Nonce = "2NuM9DBGZHb",
10
11
12 Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
13
14 each [Counter]<5,
15 each [ WebCall = Function.InvokeAfter(
16 ()=>Json.Document(Web.Contents(Url,
17 [Query=[limit="" & Limit & "",page="" & Text.From([Page]) & ""],
18 Headers=[Authorization="OAuth
19 oauth_consumer_key=" & consumerKey & ",
20 oauth_token=" & Token & ",
21 oauth_signature_method=" & SignatureMethod & ",
22 oauth_timestamp=" & TimeStamp & ",
23 oauth_nonce=" & Nonce & ",
24 oauth_version=""1.0"",
25 oauth_signature=" & Signature & "
26 "]
27 ])),
28 #duration(0,0,0,1)),
29 Page = [Page]+1,
30 Counter = [Counter]+1
31 ]
32 ) ,1),
33 Table= Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
34
35 #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"WebCall"}, {"WebCall"}),
36 #"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "57", "58", "59", "60", "61", "63", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", "111", "112"}),
37 #"Transposed Table" = Table.Transpose(#"Expanded WebCall"),
38 #"Expanded Column2" = Table.ExpandRecordColumn(#"Transposed Table", "Column1", {"entity_id", "created_at"}, {"Column1.entity_id", "Column1.created_at"}),
39 #"Removed Columns" = Table.RemoveColumns(#"Expanded Column2",{"Column2", "Column3", "Column4"})
40in
41 #"Removed Columns"