一般来说，并不是所有的数据库厂商都与微软达成了商务合作，这就导致市面上还存在一部分数据源无法通过PowerBI内置的连接器进行数据连接。
但几乎所有的数据库，都可以通过各种各样的方式，提供给外界一个可以进行数据交互的接口，也就是说我们常说的API，而恰好，PowerBI是可以对API数据进行处理的。
API，应用程序编程接口，是企业一种主流的数据传输方式，避免了通过账户密码的方式给数据库带来的安全隐患和性能问题，也是针对上面问题的答案。
对比通过数据库账户密码获取数据的方式，API具备如下优点：
-
避免通过账户密码连接数据，杜绝了安全隐患
-
避免对数据库进行查询操作，减轻了数据库负载压力
函数介绍
语法如下
let
RequestUrl = "请求的网址",
GetQuery = [参数1 = "条件1", 参数2 = "条件2"],
PostContent = "参数1=条件1&参数2=条件2",
Headers = [AccessToken = "Token的令牌", #"Content-Type" = "application/x-www-form-urlencoded"],
Result = Json.Document(
Web.Contents(RequestUrl, [Headers = Headers, Query = GetQuery, Content = Text.ToBinary(PostContent)])
)
in
Result
Web.Contents参数介绍
-
RequestUrl就是需要请求API的网址-
如果是内部自开发的，那么询问开发者使用方法即可
-
如果是外部公网的话，那么需要利用F12查询相关配置信息
-
Payload是目前存放POST请求参数的位置，之前都是存放在Headers下面
-
-
GetQuery就是查询中我们需要使用到的参数，一般是针对GET的API类型，通常是网址问号后面的部分- 例如：
www.baidu.com/s?wd=白茶，“wd=白茶”这部分就是参数
- 例如：
-
PostContent是我们需要提交的数据，一旦填写这个参数，Url的请求会自动从GET转化为POST类型 -
Headers是请求头，一般所有的API都是需要填写这部分的，包括Token、Content-Type等 -
Content -Type这个参数，要看请求类型-
如果是GET类型的API请求，那么这个参数可以省略
-
如果是POST类型的API请求，那么这个参数是必填项
-
如果参数里面有"-"、空格、或者是一些特殊符号，那么就需要添加#作为前缀声明
-
-
[]是PowerQuery的基本语法，即为Record，返回的是行数据 -
Web.Contents返回的结果是Json数据，我们利用Json.Document将其转化为结构数据 -
Result返回的结果，可以根据自己的需求，决定数据深化程度调整代码 -
某些API需要我们先获取一些条件，例如Token、Pages等一些其他先决条件
-
某些API需要我们循环遍历，例如获取Pages不同页面的相同信息，最终汇总一下
注意事项[1]
-
API请求以GET类型或POST类型的形式发出
-
POST类型请求只能匿名发出
案例
案例1：GET多页面数据合并
http://quote.stockstar.com/stock/ranklist_a_3_1_1.html
结果
代码
let
RequestUrl = "http://quote.stockstar.com/stock/ranklist_a_3_1_",
Function = (Pages) =>
let
GetData = Web.Contents(RequestUrl & Text.From(Pages) & ".html"),
UrlTable = Web.Page(GetData){0}[Data],
RemoveLastNData = Table.RemoveLastN(UrlTable, 1),
SelectColumns = Table.SelectColumns(
RemoveLastNData, {"代码", "简称", "流通市值(万元)", "总市值(万元)", "流通股本(万元)", "总股本(万元)"}
)
in
SelectColumns,
Result = Table.Combine(List.Transform({1..2}, Function))
in
Result
解析
-
RequestUrl就是需要请求API的网址- 当前网址在浏览器查看可以知道是GET类型请求，且不需要Token验证
-
Funcation添加了一个自定义函数，参数名为Pages，用来处理请求API-
GetData是请求API的基本语法，因为API网址翻页是动态的，所以将其页面标识转化为变量Pages，当前返回结果为Html页面结构 -
UrlTable是利用Web.Page函数，将Html源码解析为表格结构 -
RemoveLastNData是为了删除每一页最后一行的多余数据 -
SelectColumns是为了删除其他不需要的空白列
-
-
Result开始调用上面的自定义函数，用来循环合并数据
-
List.Transform可以让第一参数里面的每个值，都执行第二参数的操作 -
{1..2}是利用“…”构建了连续的列表，“…”前后分别代表开始和结束 -
Table.Combine的作用是为了合并多个表
-
案例2：POST多页面数据合并
http://www.drugfuture.com/cndrug/national.aspx?ApprovalDateStart=2016-01-01&ApprovalDateEnd=2016-12-31
结果
代码
let
RequestUrl = "http://www.drugfuture.com/cndrug/national.aspx",
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
GetQuery = [ApprovalDateStart = "2016-01-01", ApprovalDateEnd = "2016-12-31"],
Function = (Pages) =>
let
PostContent = "__EVENTTARGET=GridViewNational&__EVENTARGUMENT=Page%24"
& Text.From(Pages)
& "&__VIEWSTATE=%2FwEPDwUKMTg0ODM0MjIwOQ8WAh4Oc29ydEV4cHJlc3Npb24FEUFwcHJvdmFsRGF0ZSBERVNDFgICAw9kFggCAQ88KwARAwAPFgQeC18hRGF0YUJvdW5kZx4LXyFJdGVtQ291bnQCkRJkARAWABYAFgAMFCsAABYCZg9kFj4CAQ9kFhxmDw8WAh4EVGV4dAUBMWRkAgEPDxYCHwMFFeWbveiNr%2BWHhuWtl1o0NTAyMTYzNGRkAgIPDxYCHwMFBiZuYnNwO2RkAgMPDxYCHwMFDjg2OTA1MTE3MDAwNDI3ZGQCBA8PFgIfAwUGJm5ic3A7ZGQCBQ8PFgIfAwUP6KGl6IK%2B5by66Lqr54mHZGQCBg8PFgIfAwUGJm5ic3A7ZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUe5bm%2F6KW%2F5Y%2BM6JqB6I2v5Lia5pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUe5bm%2F6KW%2F5p2l5a6%2B5biC56uL5paw6LevMzg45Y%2B3ZGQCCg8PFgIfAwUELS0tLWRkAgsPDxYCHwMFEeeJh%2BWJgijns5booaPniYcpZGQCDA8PFgIfAwUG5Lit6I2vZGQCDQ8PFgIfAwUKMjAxNi0wOC0wNmRkAgIPZBYcZg8PFgIfAwUBMmRkAgEPDxYCHwMFFeWbveiNr%2BWHhuWtl0g0NTAyMDE5NGRkAgIPDxYCHwMFBiZuYnNwO2RkAgMPDxYCHwMFDjg2OTA1MTU2MDAxMjI1ZGQCBA8PFgIfAwUGJm5ic3A7ZGQCBQ8PFgIfAwUb5rOo5bCE55So56Gr6YW46Zi%2F57Gz5Y2h5pifZGQCBg8PFgIfAwUeQW1pa2FjaW4gU3VsZmF0ZSBmb3IgSW5qZWN0aW9uZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUs5bm%2F6KW%2F5qKn5bee5Yi26I2vKOmbhuWboinogqHku73mnInpmZDlhazlj7hkZAIJDw8WAh8DBSjlub%2Fopb%2Fmoqflt57lt6XkuJrlm63ljLrlt6XkuJrlpKfpgZMx5Y%2B3ZGQCCg8PFgIfAwURMC4yZygyMOS4h%2BWNleS9jSlkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCAw9kFhxmDw8WAh8DBQEzZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDQ1MDIwMTQ5ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxNTYwMDE4NjdkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRXms6jlsITnlKjlpLTlraLmi4nlrppkZAIGDw8WAh8DBRdDZWZyYWRpbmUgRm9yIEluamVjdGlvbmRkAgcPDxYCHwMFBiZuYnNwO2RkAggPDxYCHwMFLOW5v%2Bilv%2Baip%2BW3nuWItuiNryjpm4blm6Ip6IKh5Lu95pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUo5bm%2F6KW%2F5qKn5bee5bel5Lia5Zut5Yy65bel5Lia5aSn6YGTMeWPt2RkAgoPDxYCHwMFBDAuNWdkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCBA9kFhxmDw8WAh8DBQE0ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDQ1MDIwMTUyZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxNTYwMDIwMDBkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRjms6jlsITnlKjlpLTlraLllJHmnpfpkqBkZAIGDw8WAh8DBR5DZWZhem9saW4gU29kaXVtIGZvciBJbmplY3Rpb25kZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSzlub%2Fopb%2Fmoqflt57liLboja8o6ZuG5ZuiKeiCoeS7veaciemZkOWFrOWPuGRkAgkPDxYCHwMFKOW5v%2Bilv%2Baip%2BW3nuW3peS4muWbreWMuuW3peS4muWkp%2BmBkzHlj7dkZAIKDw8WAh8DBRgxLjBnKOaMiUMxNEgxNE44TzRTM%2BiuoSlkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCBQ9kFhxmDw8WAh8DBQE1ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDQ1MDIwMTUwZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxNTYwMDE3MDZkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRXms6jlsITnlKjlpLTlraLmi4nlrppkZAIGDw8WAh8DBRdDZWZyYWRpbmUgRm9yIEluamVjdGlvbmRkAgcPDxYCHwMFBiZuYnNwO2RkAggPDxYCHwMFLOW5v%2Bilv%2Baip%2BW3nuWItuiNryjpm4blm6Ip6IKh5Lu95pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUo5bm%2F6KW%2F5qKn5bee5bel5Lia5Zut5Yy65bel5Lia5aSn6YGTMeWPt2RkAgoPDxYCHwMFBDEuMGdkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCBg9kFhxmDw8WAh8DBQE2ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDQ1MDIwMTUxZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxNTYwMDE4MDVkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRjms6jlsITnlKjlpLTlraLllJHmnpfpkqBkZAIGDw8WAh8DBR5DZWZhem9saW4gU29kaXVtIGZvciBJbmplY3Rpb25kZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSzlub%2Fopb%2Fmoqflt57liLboja8o6ZuG5ZuiKeiCoeS7veaciemZkOWFrOWPuGRkAgkPDxYCHwMFKOW5v%2Bilv%2Baip%2BW3nuW3peS4muWbreWMuuW3peS4muWkp%2BmBkzHlj7dkZAIKDw8WAh8DBRgwLjVnKOaMiUMxNEgxNE44TzRTM%2BiuoSlkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCBw9kFhxmDw8WAh8DBQE3ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDQ1MDIwNzc5ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxNTYwMDEwODlkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRjms6jlsITnlKjlpLTlraLlk4zpha7pkqBkZAIGDw8WAh8DBSFDZWZvcGVyYXpvbmUgU29kaXVtIGZvciBJbmplY3Rpb25kZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSzlub%2Fopb%2Fmoqflt57liLboja8o6ZuG5ZuiKeiCoeS7veaciemZkOWFrOWPuGRkAgkPDxYCHwMFKOW5v%2Bilv%2Baip%2BW3nuW3peS4muWbreWMuuW3peS4muWkp%2BmBkzHlj7dkZAIKDw8WAh8DBRgxLjBnKOaMiUMyNUgyN045TzhTMuiuoSlkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCCA9kFhxmDw8WAh8DBQE4ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDQ1MDIwNzc4ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxNTYwMDA5NjlkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRjms6jlsITnlKjlpLTlraLlk4zpha7pkqBkZAIGDw8WAh8DBSFDZWZvcGVyYXpvbmUgU29kaXVtIGZvciBJbmplY3Rpb25kZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSzlub%2Fopb%2Fmoqflt57liLboja8o6ZuG5ZuiKeiCoeS7veaciemZkOWFrOWPuGRkAgkPDxYCHwMFKOW5v%2Bilv%2Baip%2BW3nuW3peS4muWbreWMuuW3peS4muWkp%2BmBkzHlj7dkZAIKDw8WAh8DBRgwLjVnKOaMiUMyNUgyN045TzhTMuiuoSlkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCCQ9kFhxmDw8WAh8DBQE5ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDQ1MDIwOTM0ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxNTYwMDA5MDdkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRjms6jlsITnlKjlpLTlraLlk4zpha7pkqBkZAIGDw8WAh8DBSFDZWZvcGVyYXpvbmUgU29kaXVtIGZvciBJbmplY3Rpb25kZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSzlub%2Fopb%2Fmoqflt57liLboja8o6ZuG5ZuiKeiCoeS7veaciemZkOWFrOWPuGRkAgkPDxYCHwMFKOW5v%2Bilv%2Baip%2BW3nuW3peS4muWbreWMuuW3peS4muWkp%2BmBkzHlj7dkZAIKDw8WAh8DBRgyLjBnKOaMiUMyNUgyN045TzhTMuiuoSlkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCCg9kFhxmDw8WAh8DBQIxMGRkAgEPDxYCHwMFFeWbveiNr%2BWHhuWtl0g0NTAyMTMzOGRkAgIPDxYCHwMFBiZuYnNwO2RkAgMPDxYCHwMFDjg2OTA1MTU2MDAwMzEwZGQCBA8PFgIfAwUGJm5ic3A7ZGQCBQ8PFgIfAwUb5rOo5bCE55So5Lmz57OW6YW457qi6ZyJ57SgZGQCBg8PFgIfAwUnRXJ5dGhyb215Y2luIExhY3RvYmlvbmF0ZSBmb3IgSW5qZWN0aW9uZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUs5bm%2F6KW%2F5qKn5bee5Yi26I2vKOmbhuWboinogqHku73mnInpmZDlhazlj7hkZAIJDw8WAh8DBSjlub%2Fopb%2Fmoqflt57lt6XkuJrlm63ljLrlt6XkuJrlpKfpgZMx5Y%2B3ZGQCCg8PFgIfAwURMC4zZygzMOS4h%2BWNleS9jSlkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA2LTI4ZGQCCw9kFhxmDw8WAh8DBQIxMWRkAgEPDxYCHwMFFeWbveiNr%2BWHhuWtl0g0NTAyMTMzOWRkAgIPDxYCHwMFBiZuYnNwO2RkAgMPDxYCHwMFDjg2OTA1MTU2MDAwMzI3ZGQCBA8PFgIfAwUGJm5ic3A7ZGQCBQ8PFgIfAwUb5rOo5bCE55So5Lmz57OW6YW457qi6ZyJ57SgZGQCBg8PFgIfAwUnRXJ5dGhyb215Y2luIExhY3RvYmlvbmF0ZSBmb3IgSW5qZWN0aW9uZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUs5bm%2F6KW%2F5qKn5bee5Yi26I2vKOmbhuWboinogqHku73mnInpmZDlhazlj7hkZAIJDw8WAh8DBSjlub%2Fopb%2Fmoqflt57lt6XkuJrlm63ljLrlt6XkuJrlpKfpgZMx5Y%2B3ZGQCCg8PFgIfAwUSMC4yNWcoMjXkuIfljZXkvY0pZGQCCw8PFgIfAwUJ5rOo5bCE5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNi0yOGRkAgwPZBYcZg8PFgIfAwUCMTJkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjMwMjAxMzRkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwMzcxMTAwMDQ5MmRkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFD%2Bmdnuivuui0neeJueeJh2RkAgYPDxYCHwMFE0Zlbm9maWJyYXRlIFRhYmxldHNkZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSrlk4joja%2Fpm4blm6LkuInnsr7liLboja%2Flm5vljoLmnInpmZDlhazlj7hkZAIJDw8WAh8DBSPlk4jlsJTmu6jluILpgZPph4zljLrkuIrmsZ%2FooZc1MOWPt2RkAgoPDxYCHwMFBDAuMWdkZAILDw8WAh8DBQ7niYfliYIo57Sg54mHKWRkAgwPDxYCHwMFDOWMluWtpuiNr%2BWTgWRkAg0PDxYCHwMFCjIwMTYtMDYtMjJkZAIND2QWHGYPDxYCHwMFAjEzZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDIwMDgzNTIxZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDM3NzYwMDA2OTdkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRLokaHokITns5bms6jlsITmtrJkZAIGDw8WAh8DBRFHbHVjb3NlIEluamVjdGlvbmRkAgcPDxYCHwMFBiZuYnNwO2RkAggPDxYCHwMFJ%2Bm7kem%2Bmeaxn%2BmHkeS5neiNr%2BS4muiCoeS7veaciemZkOWFrOWPuGRkAgkPDxYCHwMFJ%2Bm7kem%2Bmeaxn%2Becgem4oeilv%2BW4guWvhuWxseW4guijtOW%2Bt%2BmVh2RkAgoPDxYCHwMFCDEwMG1sOjVnZGQCCw8PFgIfAwUJ5rOo5bCE5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNi0yMWRkAg4PZBYcZg8PFgIfAwUCMTRkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAwNjYyMzlkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwNTY2MDAwMDEwM2RkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFEuiBmue7tOmFrueimOa6tua2smRkAgYPDxYCHwMFGFBvdmlkb25lIGxvZGluZSBTb2x1dGlvbmRkAgcPDxYCHwMFBiZuYnNwO2RkAggPDxYCHwMFHuS6keWNl%2BmHkeafr%2BWItuiNr%2BaciemZkOWFrOWPuGRkAgkPDxYCHwMFG%2BaYhuaYjuW4guWvu%2BeUuOWOv%2BWhmOWtkOmVh2RkAgoPDxYCHwMFCDEwMG1sOjVnZGQCCw8PFgIfAwUG5rSX5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNi0xNmRkAg8PZBYcZg8PFgIfAwUCMTVkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdaNDUwMjA3MjRkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwNTEzMTAwMDIzNmRkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFDOWls%2BWuneiDtuWbimRkAgYPDxYCHwMFBiZuYnNwO2RkAgcPDxYCHwMFBiZuYnNwO2RkAggPDxYCHwMFJOW5v%2Bilv%2BemheaWueiNr%2BS4muiCoeS7veaciemZkOWFrOWPuGRkAgkPDxYCHwMFL%2BW5v%2Bilv%2Bahguael%2BW4guWFqOW3nuWOv%2BWFqOW3numVh%2BWfjuWNl%2Bi3rzM45Y%2B3ZGQCCg8PFgIfAwUN5q%2BP57KS6KOFMC4zZ2RkAgsPDxYCHwMFCeiDtuWbiuWJgmRkAgwPDxYCHwMFBuS4reiNr2RkAg0PDxYCHwMFCjIwMTYtMDYtMTBkZAIQD2QWHGYPDxYCHwMFAjE2ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDIwMDczNDYyZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDM3MjYwMDA3MjJkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRLog7johbrogr3ogqDmurbniYdkZAIGDw8WAh8DBSRUaHltb3BlcHRpZGVzIEVudGVyaWMtY29hdGVkIFRhYmxldHNkZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSHpu5HpvpnmsZ%2Fov6rpvpnliLboja%2FmnInpmZDlhazlj7hkZAIJDw8WAh8DBTDpu5HpvpnmsZ%2FnnIHlronovr7luILljJflm5vpgZPooZfvvIjljJflm5vot6%2FvvIlkZAIKDw8WAh8DBQQxMG1nZGQCCw8PFgIfAwUG54mH5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNS0wNWRkAhEPZBYcZg8PFgIfAwUCMTdkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAwNjQ1MzhkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwMzcyNjAwMDYwOWRkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFFeiEkeibi%2BeZveawtOino%2BeJqeeJh2RkAgYPDxYCHwMFIkNlcmVicm9wcm90ZWluIEh5ZHJvbHlzYXRlIFRhYmxldHNkZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSHpu5HpvpnmsZ%2Fov6rpvpnliLboja%2FmnInpmZDlhazlj7hkZAIJDw8WAh8DBR7pu5HpvpnmsZ%2FnnIHlronovr7luILljJflm5vot69kZAIKDw8WAh8DBTE2LjVtZyjmjInmsKjln7rmsK7orqHnrpcpOjE0LjRtZyjmjInmgLvmsK7orqHnrpcpZGQCCw8PFgIfAwUG54mH5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNC0yNWRkAhIPZBYcZg8PFgIfAwUCMThkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAwNjAwNzBkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwMzY2MjAwMDA1M2RkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFFeWlpeaLieilv%2BWdpuazqOWwhOa2smRkAgYPDxYCHwMFFE94aXJhY2V0YW0gSW5qZWN0aW9uZGQCBw8PFgIfAwUJ5qyn5YWw5ZCMZGQCCA8PFgIfAwUn5ZOI5bCU5ruo5LiJ6IGU6I2v5Lia6IKh5Lu95pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUk5ZOI5bCU5ruo5biC5Yip5rCR5byA5Y%2BR5Yy65YyX5Lqs6LevZGQCCg8PFgIfAwUGNW1sOjFnZGQCCw8PFgIfAwUJ5rOo5bCE5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNC0yNWRkAhMPZBYcZg8PFgIfAwUCMTlkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAwNjQwNDRkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwMzcyNjAwMDIxMGRkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFDOWwvOm6puinkuael2RkAgYPDxYCHwMFC05pY2VyZ29saW5lZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUh6buR6b6Z5rGf6L%2Bq6b6Z5Yi26I2v5pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUe6buR6b6Z5rGf55yB5a6J6L6%2B5biC5YyX5Zub6LevZGQCCg8PFgIfAwUELS0tLWRkAgsPDxYCHwMFCeWOn%2BaWmeiNr2RkAgwPDxYCHwMFDOWMluWtpuiNr%2BWTgWRkAg0PDxYCHwMFCjIwMTYtMDQtMjVkZAIUD2QWHGYPDxYCHwMFAjIwZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDIwMDY0MDMxZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDM3MjYwMDA1OTNkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBQnnu4bovpvohJFkZAIGDw8WAh8DBQdBc2Fyb25lZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUh6buR6b6Z5rGf6L%2Bq6b6Z5Yi26I2v5pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUe6buR6b6Z5rGf55yB5a6J6L6%2B5biC5YyX5Zub6LevZGQCCg8PFgIfAwUELS0tLWRkAgsPDxYCHwMFCeWOn%2BaWmeiNr2RkAgwPDxYCHwMFDOWMluWtpuiNr%2BWTgWRkAg0PDxYCHwMFCjIwMTYtMDQtMjVkZAIVD2QWHGYPDxYCHwMFAjIxZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XWjIwMDY0NDA3ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxMTkwMDAwMjlkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBQ%2FpgJrnqo3pvLvngo7niYdkZAIGDw8WAh8DBQYmbmJzcDtkZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSTlub%2Fopb%2Fmn7Plt57plb%2FlronliLboja%2FmnInpmZDlhazlj7hkZAIJDw8WAh8DBSTlub%2Fopb%2Fmn7Plt57luILono3lronljr%2Fln47kupTph4zkuq1kZAIKDw8WAh8DBQ7niYfoiq%2Fph40wLjM1Z2RkAgsPDxYCHwMFBueJh%2BWJgmRkAgwPDxYCHwMFBuS4reiNr2RkAg0PDxYCHwMFCjIwMTYtMDQtMTRkZAIWD2QWHGYPDxYCHwMFAjIyZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XWjIwMDYzNDU2ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUyMDUwMDAwMzJkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBQ3nu7RD6ZO257%2BY54mHZGQCBg8PFgIfAwUGJm5ic3A7ZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUe5qGC5p6X6I6x6Iy16I2v5Lia5pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUg5qGC5p6X5biC5YW05a6J5Y6%2F5rmY5rGf6LevMTjlj7dkZAIKDw8WAh8DBVzln7rniYfph40wLjI4ZyjlkKvnu7TnlJ%2FntKBDIDQ5LjVtZ%2BOAgeWvueS5memFsOawqOWfuumFmjEwNW1n44CB6ams5p2l6YW45rCv6Iuv6YKj5pWPMS4wNW1nKWRkAgsPDxYCHwMFBueJh%2BWJgmRkAgwPDxYCHwMFBuS4reiNr2RkAg0PDxYCHwMFCjIwMTYtMDQtMTRkZAIXD2QWHGYPDxYCHwMFAjIzZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XWjIwMDY0Mzc3ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxMTkwMDAxODBkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBQ%2FniZvpu4TkuIrmuIXniYdkZAIGDw8WAh8DBQYmbmJzcDtkZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSTlub%2Fopb%2Fmn7Plt57plb%2FlronliLboja%2FmnInpmZDlhazlj7hkZAIJDw8WAh8DBSTlub%2Fopb%2Fmn7Plt57luILono3lronljr%2Fln47kupTph4zkuq1kZAIKDw8WAh8DBRHmr4%2Fln7rniYfph40wLjI1Z2RkAgsPDxYCHwMFBueJh%2BWJgmRkAgwPDxYCHwMFBuS4reiNr2RkAg0PDxYCHwMFCjIwMTYtMDQtMTRkZAIYD2QWHGYPDxYCHwMFAjI0ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XWjIwMDY0MjkwZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDM5MDkwMDA0MzNkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBQ%2FlhqDlv4PkuIPlkbPniYdkZAIGDw8WAh8DBQYmbmJzcDtkZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSHlhoXokpnlj6Tokpnoja%2FogqHku73mnInpmZDlhazlj7hkZAIJDw8WAh8DBS3pgJrovr3nu4%2FmtY7mioDmnK%2FlvIDlj5HljLrovr3msrPlpKfooZfopb%2FmrrVkZAIKDw8WAh8DBQ7mr4%2FniYfph40wLjMxZ2RkAgsPDxYCHwMFFeeJh%2BWJgu%2B8iOiWhOiGnOiho%2B%2B8iWRkAgwPDxYCHwMFBuS4reiNr2RkAg0PDxYCHwMFCjIwMTYtMDQtMTJkZAIZD2QWHGYPDxYCHwMFAjI1ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XWjIwMDYzMzk0ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDM5MDkwMDA0NDBkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBRLmmI7nm67ljYHlha3lkbPkuLhkZAIGDw8WAh8DBQYmbmJzcDtkZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSHlhoXokpnlj6Tokpnoja%2FogqHku73mnInpmZDlhazlj7hkZAIJDw8WAh8DBS3pgJrovr3nu4%2FmtY7mioDmnK%2FlvIDlj5HljLrovr3msrPlpKfooZfopb%2FmrrVkZAIKDw8WAh8DBQ3mr48xMOS4uOmHjTJnZGQCCw8PFgIfAwUS5Li45YmC77yI5rC05Li477yJZGQCDA8PFgIfAwUG5Lit6I2vZGQCDQ8PFgIfAwUKMjAxNi0wNC0xMmRkAhoPZBYcZg8PFgIfAwUCMjZkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAwNjYxNzFkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwMzcyNjAwMDcxNWRkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFFeazqOWwhOeUqOehq%2BaZrue9l%2BWugWRkAgYPDxYCHwMFF1Rpb3Byb25pbiBmb3IgSW5qZWN0aW9uZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUh6buR6b6Z5rGf6L%2Bq6b6Z5Yi26I2v5pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwUe6buR6b6Z5rGf55yB5a6J6L6%2B5biC5YyX5Zub6LevZGQCCg8PFgIfAwUEMC4xZ2RkAgsPDxYCHwMFCeazqOWwhOWJgmRkAgwPDxYCHwMFDOWMluWtpuiNr%2BWTgWRkAg0PDxYCHwMFCjIwMTYtMDQtMTFkZAIbD2QWHGYPDxYCHwMFAjI3ZGQCAQ8PFgIfAwUV5Zu96I2v5YeG5a2XSDIwMDY0MTg2ZGQCAg8PFgIfAwUGJm5ic3A7ZGQCAw8PFgIfAwUOODY5MDUxOTQwMDAwNTFkZAIEDw8WAh8DBQYmbmJzcDtkZAIFDw8WAh8DBQzlpLTlraLnoavohJJkZAIGDw8WAh8DBQ5DZWZhdGhpYW1pZGluZWRkAgcPDxYCHwMFBiZuYnNwO2RkAggPDxYCHwMFHuW5v%2Bilv%2BenkeS8puWItuiNr%2BaciemZkOWFrOWPuGRkAgkPDxYCHwMFOuahguael%2BW4guawuOemj%2BWOv%2BiLj%2BahpemVh%2BiLj%2BahpeW3peS4muWbreawtOiNhuS4nOi3rzblj7dkZAIKDw8WAh8DBQQtLS0tZGQCCw8PFgIfAwUJ5Y6f5paZ6I2vZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNC0xMWRkAhwPZBYcZg8PFgIfAwUCMjhkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAwNTk0NDFkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwMzcyNjAwMDczOWRkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFGOejt%2BmFuOW3neiKjuWXquazqOWwhOa2smRkAgYPDxYCHwMFIExpZ3VzdHJhemluZSBQaG9zcGhhdGUgSW5qZWN0aW9uZGQCBw8PFgIfAwUGJm5ic3A7ZGQCCA8PFgIfAwUh6buR6b6Z5rGf6L%2Bq6b6Z5Yi26I2v5pyJ6ZmQ5YWs5Y%2B4ZGQCCQ8PFgIfAwU56buR6b6Z5rGf55yB57ul5YyW5biC5a6J6L6%2B5biC5YyX5Zub6YGT6KGX77yI5YyX5Zub6Lev77yJZGQCCg8PFgIfAwUIMm1sOjUwbWdkZAILDw8WAh8DBQnms6jlsITliYJkZAIMDw8WAh8DBQzljJblraboja%2Flk4FkZAINDw8WAh8DBQoyMDE2LTA0LTExZGQCHQ9kFhxmDw8WAh8DBQIyOWRkAgEPDxYCHwMFFeWbveiNr%2BWHhuWtl0gyMDA1OTg3N2RkAgIPDxYCHwMFBiZuYnNwO2RkAgMPDxYCHwMFDjg2OTAzNzI2MDAwNjE2ZGQCBA8PFgIfAwUGJm5ic3A7ZGQCBQ8PFgIfAwUY5rOo5bCE55So5aWl5omO5qC86Zu36ZKgZGQCBg8PFgIfAwUdT3phZ3JlbCBTb2RpdW0gZm9yICBJbmplY3Rpb25kZAIHDw8WAh8DBQYmbmJzcDtkZAIIDw8WAh8DBSHpu5HpvpnmsZ%2Fov6rpvpnliLboja%2FmnInpmZDlhazlj7hkZAIJDw8WAh8DBTnpu5HpvpnmsZ%2FnnIHnu6XljJbluILlronovr7luILljJflm5vpgZPooZfvvIjljJflm5vot6%2FvvIlkZAIKDw8WAh8DBR8yMG1n77yI5oyJ5aWl5omO5qC86Zu36ZKg6K6h77yJZGQCCw8PFgIfAwUJ5rOo5bCE5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNC0xMWRkAh4PZBYcZg8PFgIfAwUCMzBkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAxMTMyMTZkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBQ44NjkwNTE5OTAwMDE4NmRkAgQPDxYCHwMFBiZuYnNwO2RkAgUPDxYCHwMFFeiDtuS9k%2BaenOiDtumTi%2BiDtuWbimRkAgYPDxYCHwMFIUNvbGxvaWRhbCBCaXNtdXRoIFBlY3RpbiBDYXBzdWxlc2RkAgcPDxYCHwMFBiZuYnNwO2RkAggPDxYCHwMFHuahguael%2BWNjuS%2FoeWItuiNr%2BaciemZkOWFrOWPuGRkAgkPDxYCHwMFMuW5v%2Bilv%2Bahguael%2BeBteW3neWOv%2BeBteW3nemVh%2BeBteWMl%2Bi3r%2BS4gOihlzg55Y%2B3ZGQCCg8PFgIfAwUPNTBtZyjku6Xpk4vorqEpZGQCCw8PFgIfAwUJ6IO25ZuK5YmCZGQCDA8PFgIfAwUM5YyW5a2m6I2v5ZOBZGQCDQ8PFgIfAwUKMjAxNi0wNC0xMWRkAh8PDxYCHgdWaXNpYmxlaGRkAgMPDxYCHwMFXeespuWQiOadoeS7tueahOaVsOaNruWFsTxzcGFuIHN0eWxlPSJjb2xvcjpyZWQiPjIzMjE8L3NwYW4%2B5p2hIeeCueWHu%2BWIl%2BWQjeWPr%2BiHquWKqOaOkuW6j%2BOAgmRkAgUPPCsAEQMADxYEHwFnHwICAWQBEBYAFgAWAAwUKwAAFgJmD2QWBgIBD2QWGmYPDxYCHwMFATFkZAIBDw8WAh8DBRXlm73oja%2Flh4blrZdIMjAwNTc1OTZkZAICDw8WAh8DBQYmbmJzcDtkZAIDDw8WAh8DBSLmnKzmibnlh4bmloflj7fkuo4yMDE1LTEwLTIy5rOo6ZSAZGQCBA8PFgIfAwUS5rCv5YyW6ZKg5rOo5bCE5rayZGQCBQ8PFgIfAwUZU29kaXVtIENobG9yaWRlIEluamVjdGlvbmRkAgYPDxYCHwMFBiZuYnNwO2RkAgcPDxYCHwMFHua5luWNl%2BenkeS8puWItuiNr%2BaciemZkOWFrOWPuGRkAggPDxYCHwMFM%2Ba5luWNl%2BecgeWys%2BmYs%2Be7j%2Ba1juaKgOacr%2BW8gOWPkeWMuuW6t%2BeOi%2BW3peS4muWbrWRkAgkPDxYCHwMFCTEwbWw6OTBtZ2RkAgoPDxYCHwMFCeazqOWwhOWJgmRkAgsPDxYCHwMFDOWMluWtpuiNr%2BWTgWRkAgwPDxYCHwMFCjIwMTYtMDEtMDZkZAICDw8WAh8EaGRkAgMPDxYCHwRoZGQCBw8PFgIfAwVa56ym5ZCI5p2h5Lu255qE5pWw5o2u5YWxPHNwYW4gc3R5bGU9ImNvbG9yOnJlZCI%2BMTwvc3Bhbj7mnaEh54K55Ye75YiX5ZCN5Y%2Bv6Ieq5Yqo5o6S5bqP44CCZGQYAgUXR3JpZFZpZXdOYXRpb25hbEV4cGlyZWQPPCsADAEIAgFkBRBHcmlkVmlld05hdGlvbmFsDzwrAAwBCAJOZDQtgbNwk9FoE66VwqSIZ%2Bo%2Bhczy&__VIEWSTATEGENERATOR=1B716CAC",
GetData = Web.Contents(
RequestUrl, [Headers = Headers, Query = GetQuery, Content = Text.ToBinary(PostContent)]
),
UrlTable = Web.Page(Text.FromBinary(GetData)){0}[Data],
RemoveLastNData = Table.RemoveLastN(UrlTable, 1)
in
RemoveLastNData,
Result = Table.Combine(List.Transform({1..2}, Function))
in
Result
解析
-
RequestUrl就是需要请求API的网址- 当前网址在浏览器查看可以知道是POST类型请求，且不需要Token验证
-
Headers是为了添加Content-Type，因为是POST类型请求，此参数为必填项 -
GetQuery是查询的需要使用的参数 -
Funcation添加了一个自定义函数，参数名为Pages，用来处理请求API-
PostContent是我们需要提交的数据，因为是POST类型，此项为必填项 -
在PostContent中，我们将页面标识转化为变量Pages
-
GetData是请求API的基本语法，因为是POST类型，所以利用Text.ToBinary将PostContent转化为二进制字节提交 -
要根据返回类型，灵活调整转化函数，二进制处理：
Text.FromBinary，Json处理：Json.Document，Html源码解析：Web.Page -
UrlTable是利用Text.FromBinary函数，将二进制字节转化为文本，再利用Web.Page函数提取表信息 -
RemoveLastNData是为了删除每一页最后一行的多余数据
-
-
Result开始调用上面的自定义函数，用来循环合并数据-
List.Transform可以让第一参数里面的每个值，都执行第二参数的操作 -
{1..2}是利用“…”构建了连续的列表，“…”前后分别代表开始和结束 -
Table.Combine的作用是为了合并多个表
-
特别鸣谢施阳大佬
PowerBI处理API数据这块，白茶借鉴了阳神写的文章，可以说阳神写的文章已经很全面了。
注意，
爬虫有风险，使用需谨慎，请用于正途，本文仅用作个人学习参照使用。
文章推荐
Power Query网抓详解[2]
Power Query网抓案例[3]
引用链接
[1] 注意事项: https://learn.microsoft.com/zh-cn/powerquery-m/web-contents
[2] Power Query网抓详解: https://pqfans.com/2230.html
[3] Power Query网抓案例: https://pqfans.com/207.html
2293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
