利用excel powerQuery获取在线表格数据（以包含登陆的post数据为例）

首先贴一段利用powerQuery获取需登陆网站的在线表格数据代码：

let
	url="",//必填项:网页真实的网址链接
	headers=[#"Content-Type"="",Cookie="",Referer=""], //必填项:Content-Type和登录的cookie是必填项，Referer防盗链接最好填写上
	query=[], //选填项:网址的一些查询参数，一般Requset_ URL中?后面的部分，既可以写在URl里面，也可以写在这里面
	content="",//必填项
	web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)]))
in
	web

　然后再根据集思录网站获取到相关信息补充下上面的代码：

let
	url="https://www.jisilu.cn/data/cbnew/cb_list_new/?___jsl=LST___t=1677990096350",//必填项:网页真实的网址链接
	headers=[#"Content-Type"="application/json; charset=utf-8",Cookie="kbz_newcookie=1; kbzw__user_login=7Obd08_P1ebax9aX3svk0O7Y4d-VooKvpuXK7N_u0ejF1dS****26GvzaiSpper2KPclaHD1dyumd6fqpnamtyYrqXW2cXS1qCaq56olaiVmLKgzaLOvp_G5OPi2OPDpZalp5OguNnP2Ojs3Jm6y4KnkaWnrpi42c-qrbCJ8aKri5ai5-ff3bjVw7_i6Ziun66QqZeXn77Atb2toJnh0uTRl6nbxOLmnJik2NPj5tqYqp2nkKaPp6WjmLTRx9Xr3piun66QqZc.; kbzw__Session=7js02vhqc4i5nh7l4ipqvcqjc1; Hm_lvt_164fe01b1433a19b507595a43bf58262=1677505106,1677679971,1677751686,1677990035; Hm_lpvt_164fe01b1433a19b507595a43bf58262=1677990066",Referer=""], //必填项:Content-Type和登录的cookie是必填项，Referer防盗链接最好填写上
	query=[], //选填项:网址的一些查询参数，一般Requset_ URL中?后面的部分，既可以写在URl里面，也可以写在这里面
        content="fprice=&tprice=&curr_iss_amt=&volume=&svolume=&premium_rt=&ytm_rt=&rating_cd=&is_search=N&market_cd%5B%5D=shmb&market_cd%5B%5D=shkc&market_cd%5B%5D=szmb&market_cd%5B%5D=szcy&btype=&listed=Y&qflag=N&sw_cd=&bond_ids=&rp=50&page=1",//必填项
	web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)]))
in
	web

　　上面的代码cookie部分请打开集思录网站后自己粘贴。注意：集思录网站有个cookie字段kbzw__Session每天都会变化，需要每天更新下，还没有解决，等解决了再来和大家分享。

最后，把我整理好的获取集思录网站的pq代码贴上，可以直接拿来用了：

let
    timestamp=Text.From(Int64.From(Duration.TotalSeconds(DateTime.LocalNow()-#datetime(1970,1,1,8,0,0)))),
    kbzw__Session=Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="cookie"]}[Content],{{"kbzw__Session#(tab)", type text}}){0}[#"kbzw__Session#(tab)"],
    url="https://www.jisilu.cn/data/cbnew/cb_list_new/?___jsl=LST___t=1677990096350",//必填项:网页真实的网址链接
    headers=[#"Content-Type"="application/json; charset=utf-8",Cookie="kbz_newcookie=1; kbzw__user_login=7****_P1ebax9aX3svk0O7Y4d-VooKvpuXK7N_u0ejF1dSeqcXWktmv26GvzaiSpper2KPclaHD1dyumd6fqpnamtyYrqXW2cXS1qCaq56olaiVmLKgzaLOvp_G5OPi2OPDpZalp5OguNnP2Ojs3Jm6y4KnkaWnrpi42c-qrbCJ8aKri5ai5-ff3bjVw7_i6Ziun66QqZeXn77Atb2toJnh0uTRl6nbxOLmnJik2NPj5tqYqp2nkKaPp6WjmLTRx9Xr3piun66QqZc.; kbzw__Session="&kbzw__Session&"; Hm_lvt_164fe01b1433a19b507595a43bf58262="&timestamp&"; Hm_lpvt_164fe01b1433a19b507595a43bf58262="&timestamp&"",Referer="https://www.jisilu.cn/data/cbnew/"], //必填项:Content-Type和登录的cookie是必填项，Referer防盗链接最好填写上
    query=[], //选填项:网址的一些查询参数，一般Requset_ URL中?后面的部分，既可以写在URl里面，也可以写在这里面
    content="fprice=&tprice=&curr_iss_amt=&volume=&svolume=&premium_rt=&ytm_rt=&rating_cd=&is_search=N&market_cd%5B%5D=shmb&market_cd%5B%5D=shkc&market_cd%5B%5D=szmb&market_cd%5B%5D=szcy&btype=&listed=Y&qflag=N&sw_cd=&bond_ids=&rp=50&page=1",//必填项
    web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)])),
    #"解析的 JSON" = Json.Document(web),
    rows = #"解析的 JSON"[rows],
    转换为表 = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展开的“Column1”" = Table.ExpandRecordColumn(转换为表, "Column1", {"id", "cell"}, {"Column1.id", "Column1.cell"}),
    #"展开的“Column1.cell”" = Table.ExpandRecordColumn(#"展开的“Column1”", "Column1.cell", {"bond_id", "bond_nm", "bond_py", "price", "increase_rt", "stock_id", "stock_nm", "stock_py", "sprice", "sincrease_rt", "pb", "convert_price", "convert_value", "convert_dt", "premium_rt", "bond_premium_rt", "dblow", "adjust_condition", "sw_cd", "market_cd", "btype", "list_dt", "qflag2", "owned", "hold", "bond_value", "rating_cd", "option_value", "volatility_rate", "put_convert_price", "force_redeem_price", "convert_amt_ratio", "fund_rt", "short_maturity_dt", "year_left", "curr_iss_amt", "volume", "svolume", "turnover_rt", "ytm_rt", "put_ytm_rt", "notes", "noted", "bond_nm_tip", "redeem_icon", "last_time", "qstatus", "margin_flg", "sqflag", "pb_flag", "adj_cnt", "adj_scnt", "convert_price_valid", "convert_price_tips", "convert_cd_tip", "ref_yield_info", "adjusted", "orig_iss_amt", "price_tips", "redeem_dt", "real_force_redeem_price", "option_tip", "adjust_status", "unadj_cnt"}, {"Column1.cell.bond_id", "Column1.cell.bond_nm", "Column1.cell.bond_py", "Column1.cell.price", "Column1.cell.increase_rt", "Column1.cell.stock_id", "Column1.cell.stock_nm", "Column1.cell.stock_py", "Column1.cell.sprice", "Column1.cell.sincrease_rt", "Column1.cell.pb", "Column1.cell.convert_price", "Column1.cell.convert_value", "Column1.cell.convert_dt", "Column1.cell.premium_rt", "Column1.cell.bond_premium_rt", "Column1.cell.dblow", "Column1.cell.adjust_condition", "Column1.cell.sw_cd", "Column1.cell.market_cd", "Column1.cell.btype", "Column1.cell.list_dt", "Column1.cell.qflag2", "Column1.cell.owned", "Column1.cell.hold", "Column1.cell.bond_value", "Column1.cell.rating_cd", "Column1.cell.option_value", "Column1.cell.volatility_rate", "Column1.cell.put_convert_price", "Column1.cell.force_redeem_price", "Column1.cell.convert_amt_ratio", "Column1.cell.fund_rt", "Column1.cell.short_maturity_dt", "Column1.cell.year_left", "Column1.cell.curr_iss_amt", "Column1.cell.volume", "Column1.cell.svolume", "Column1.cell.turnover_rt", "Column1.cell.ytm_rt", "Column1.cell.put_ytm_rt", "Column1.cell.notes", "Column1.cell.noted", "Column1.cell.bond_nm_tip", "Column1.cell.redeem_icon", "Column1.cell.last_time", "Column1.cell.qstatus", "Column1.cell.margin_flg", "Column1.cell.sqflag", "Column1.cell.pb_flag", "Column1.cell.adj_cnt", "Column1.cell.adj_scnt", "Column1.cell.convert_price_valid", "Column1.cell.convert_price_tips", "Column1.cell.convert_cd_tip", "Column1.cell.ref_yield_info", "Column1.cell.adjusted", "Column1.cell.orig_iss_amt", "Column1.cell.price_tips", "Column1.cell.redeem_dt", "Column1.cell.real_force_redeem_price", "Column1.cell.option_tip", "Column1.cell.adjust_status", "Column1.cell.unadj_cnt"}),
    删除的列 = Table.RemoveColumns(#"展开的“Column1.cell”",{"Column1.id"}),
    重命名的列 = Table.RenameColumns(删除的列,{{"Column1.cell.bond_id", "代码"}, {"Column1.cell.bond_nm", "转债名称"}}),
    重命名的列1 = Table.RenameColumns(重命名的列,{{"Column1.cell.price", "现价"}, {"Column1.cell.increase_rt", "涨跌幅"}}),
    重命名的列2 = Table.RenameColumns(重命名的列1,{{"Column1.cell.stock_nm", "正股名称"}}),
    删除的列3 = Table.RemoveColumns(重命名的列2,{"Column1.cell.stock_py"}),
    重命名的列3 = Table.RenameColumns(删除的列3,{{"Column1.cell.sprice", "正股价"}, {"Column1.cell.sincrease_rt", "正股涨跌"}, {"Column1.cell.pb", "正股PB"}, {"Column1.cell.convert_price", "转股价"}, {"Column1.cell.convert_value", "转股价值"}}),
    排序的行 = Table.Sort(重命名的列3,{{"代码", Order.Ascending}}),
    删除的列4 = Table.RemoveColumns(排序的行,{"Column1.cell.convert_dt"}),
    重命名的列4 = Table.RenameColumns(删除的列4,{{"Column1.cell.premium_rt", "转股溢价率(foo)"}, {"Column1.cell.dblow", "双低"}, {"Column1.cell.rating_cd", "债券评级"}, {"Column1.cell.short_maturity_dt", "到期时间"}, {"Column1.cell.year_left", "剩余年限"}, {"Column1.cell.curr_iss_amt", "剩余规模（亿元）"}, {"Column1.cell.volume", "转债成交额（万元）"}, {"Column1.cell.turnover_rt", "换手率"}, {"Column1.cell.ytm_rt", "到期税前收益"}, {"Column1.cell.bond_nm_tip", "强赎"}, {"Column1.cell.convert_price_tips", "下修"}, {"Column1.cell.convert_cd_tip", "转股期"}, {"Column1.cell.list_dt", "上市日"}, {"Column1.cell.last_time", "数据更新时间"}}),
    删除的列5 = Table.RemoveColumns(重命名的列4,{"Column1.cell.sw_cd", "Column1.cell.market_cd", "Column1.cell.btype", "Column1.cell.qflag2", "Column1.cell.owned", "Column1.cell.hold", "Column1.cell.bond_value", "Column1.cell.option_value"}),
    重命名的列5 = Table.RenameColumns(删除的列5,{{"Column1.cell.put_convert_price", "回售触发价"}, {"Column1.cell.force_redeem_price", "强赎触发价"}, {"Column1.cell.convert_amt_ratio", "转债占比"}}),
    删除的列6 = Table.RemoveColumns(重命名的列5,{ "Column1.cell.put_ytm_rt", "Column1.cell.noted"}),
    重命名的列6 = Table.RenameColumns(删除的列6,{{"Column1.cell.redeem_icon", "不提前赎回提示"}, {"Column1.cell.orig_iss_amt", "发行规模"}}),
    删除的列7 = Table.RemoveColumns(重命名的列6,{"Column1.cell.volatility_rate", "Column1.cell.notes"}),
    重命名的列7 = Table.RenameColumns(删除的列7,{{"Column1.cell.redeem_dt", "强赎日期"}, {"Column1.cell.real_force_redeem_price", "强赎价格"}, {"Column1.cell.bond_py", "转债简写"}, {"Column1.cell.stock_id", "正股代码"}, {"Column1.cell.margin_flg", "融资融券标的"}}),
    已添加自定义 = Table.AddColumn(重命名的列7, "转股溢价率", each Number.ToText([#"转股溢价率(foo)"]/100,"P2")),
    重排序的列 = Table.ReorderColumns(已添加自定义,{"代码", "转债名称", "转债简写", "现价", "涨跌幅", "正股代码", "正股名称", "正股价", "正股涨跌", "正股PB", "转股价", "转股价值", "转股溢价率(foo)", "转股溢价率", "双低", "上市日", "债券评级", "回售触发价", "强赎触发价", "转债占比", "到期时间", "剩余年限", "剩余规模（亿元）", "转债成交额（万元）", "Column1.cell.svolume", "换手率", "到期税前收益", "强赎", "不提前赎回提示", "数据更新时间", "Column1.cell.qstatus", "融资融券标的", "Column1.cell.sqflag", "Column1.cell.pb_flag", "Column1.cell.adj_cnt", "Column1.cell.adj_scnt", "Column1.cell.convert_price_valid", "下修", "转股期", "Column1.cell.ref_yield_info", "Column1.cell.adjusted", "发行规模", "Column1.cell.price_tips", "强赎日期", "强赎价格", "Column1.cell.option_tip"}),
    删除的列1 = Table.RemoveColumns(重排序的列,{"转股溢价率(foo)"}),
    更改的类型 = Table.TransformColumnTypes(删除的列1,{{"转股溢价率", Percentage.Type}}),
    已添加自定义1 = Table.AddColumn(更改的类型, "剩余规模 × 溢价率", each [#"剩余规模（亿元）"]*[转股溢价率]),
    已添加自定义2 = Table.AddColumn(已添加自定义1, "三低评分", each [现价]*(1+[转股溢价率])*[#"剩余规模（亿元）"]),
    更改的类型1 = Table.TransformColumnTypes(已添加自定义2,{{"三低评分", Int64.Type}}),
    重命名的列8 = Table.RenameColumns(更改的类型1,{{"Column1.cell.fund_rt", "机构持仓占比"}, {"Column1.cell.price_tips", "转债价格提示"}, {"Column1.cell.bond_premium_rt", "债券溢价率"}, {"Column1.cell.adjust_condition", "下修天计数"}, {"Column1.cell.svolume", "正股成交额（万元）"}})
in
    重命名的列8