如何使用PQ获取央行某日的外币牌价

第一步：找到央行提供的查询牌价的API，并使用PQ模仿这个post请求。

(queryDate as date,page as text)=>
//请将此函数命名为"fxGetCurrencyPriceForOnePage"
let
	queryDateString=Date.ToText(queryDate,"yyyy-MM-dd"),
	url="https://srh.bankofchina.com/search/whpj/search_cn.jsp",
	h=[#"Content-Type"="application/x-www-form-urlencoded"],
	c=Text.ToBinary( "erectData=" & queryDateString & "&nothing=" & queryDateString & "&pjname=" & Uri.EscapeDataString("美元") & "&page="& page &"&head=head_620.js&bottom=bottom_591.js" ),
	getWebContent=Text.FromBinary(Web.Contents(url,[Headers=h,Content=c])),
	getTitle=List.Transform({0..6},each Text.BetweenDelimiters(Text.Clean(getWebContent),"<th>","</th>",_,0)),
    getLineCount = List.Count(Text.PositionOf(getWebContent,"<tr>",2))-3,
	getPriceData=List.Transform({0..getLineCount*7-1},each Text.BetweenDelimiters(getWebContent,"<td>","</td>",_,0)),
	splitDataToList=List.Split(getPriceData,7),
	dataTable=Table.FromRows(splitDataToList,getTitle),
    cleanText = Table.TransformColumns(dataTable,{{"货币名称", Text.Clean, type text}, {"现汇买入价", Text.Clean, type text}, {"现钞买入价", Text.Clean, type text}, {"现汇卖出价", Text.Clean, type text}, {"现钞卖出价", Text.Clean, type text}, {"中行折算价", Text.Clean, type text}, {"发布时间", Text.Clean, type text}}),
    changeType = Table.TransformColumnTypes(cleanText,{{"发布时间", type datetime}, {"现汇买入价", Currency.Type}, {"现钞买入价", Currency.Type}, {"现汇卖出价", Currency.Type}, {"现钞卖出价", Currency.Type}, {"中行折算价", Currency.Type}})
in
    changeType

第二步：

​ 因为调用一次API只获取了一个页面，同一天的牌价可能需要多个页面才能完全显示，上面这个函数只是查询到了一部分。通过调用这个函数，实现查询一整天的外汇牌价。这样就不多不少，正好是一天的数据了。

(idate as date)=>
let
    toTable = List.Generate(
        ()=> [n=1,t=Table.FromRecords({[发布时间=idate]})],
        each Date.From(List.Min([t][发布时间]))=idate,
        each [
            n=[n]+1,
            t=fxGetCurrencyPriceForOnePage(idate,Text.From([n]))
        ],
        each fxGetCurrencyPriceForOnePage(idate,Text.From([n]))
    ),
    rst=Table.SelectRows(Table.Combine(toTable),each Date.From(_[发布时间])=idate)
in
    rst