你知道吗？原来普通的Word文档里的表格也能用Power Query直接读？？！！！ / 四六文摘

昨天的文章《将WORD简历数据汇总到Excel，用Power Query竟然这么简单？》里提到，有的word文档因为是一些从网络下载的格式化文档，本身带了html的标记，所以可以直接用Web.Page函数来进行解析。

但大多数普通的word文档，是不能直接用Web.Page函数来解析的，一般需要另存为html文件，才能用Power Query来读取。

实际上，这——

因为，Power Query实在太强大，完全可以直接将普通的word文件通过二进制数据的方式读取后给（替换）加上html标记，然后通过Web.Page函数来读取！

也就是说——

普通Word文档的表格

也能用PQ直接读！！！

不过，这个过程比较复杂，对于我等普通的用户来说，只要学会抱大腿就行了（本文核心代码参考pqfans文章https://pqfans.com/2447.html）。

将下面的代码复制到高级编辑器里：

let docx=(path as text,optional index as number)=> let DecompressFiles = (ZIPFile, Position, FileToExtract, XMLSoFar) => let

MyBinaryFormat = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position),MiscHeader=BinaryFormat.Binary(18),FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),UnCompressedFileSize=BinaryFormat.Binary(4),FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),TheRest=BinaryFormat.Binary()]) otherwise null,

MyCompressedFileSize = try MyBinaryFormat(ZIPFile)[FileSize]+1 otherwise null, MyFileNameLen = try MyBinaryFormat(ZIPFile)[FileNameLen] otherwise null, MyExtrasLen = try MyBinaryFormat(ZIPFile)[ExtrasLen] otherwise null,

MyBinaryFormat2 = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position), Header=BinaryFormat.Binary(30), Filename=BinaryFormat.Text(MyFileNameLen), Extras=BinaryFormat.Binary(MyExtrasLen), Data=BinaryFormat.Binary(MyCompressedFileSize), TheRest=BinaryFormat.Binary()]) otherwise null,

MyFileName = try MyBinaryFormat2(ZIPFile)[Filename] otherwise null, GetDataToDecompress = try MyBinaryFormat2(ZIPFile)[Data] otherwise null, DecompressData = try Binary.Decompress(GetDataToDecompress, Compression.Deflate) otherwise null, NewPosition = try Position + 30 + MyFileNameLen + MyExtrasLen + MyCompressedFileSize - 1 otherwise null, ImportedXML = DecompressData, AddedCustom = try Table.AddColumn(ImportedXML, "Filename", each MyFileName) otherwise ImportedXML,

AppendedQuery = if ImportedXML = null then XMLSoFar else if (MyFileName = FileToExtract) then AddedCustom else if (FileToExtract = "") and Position <> 0 then Table.Combine({AddedCustom, XMLSoFar}) else AddedCustom

in

if (MyFileName = FileToExtract) or (AppendedQuery = XMLSoFar) then AppendedQuery else @DecompressFiles(ZIPFile, NewPosition, FileToExtract, AppendedQuery),

MyXML = Lines.FromBinary(DecompressFiles(File.Contents(path), 0, "word/document.xml", "")){1},

ReplaceList = {{"<w:tbl>","<table>"},{"</w:tbl>","</table>"},{"<w:tr>","<tr>"},{"</w:tr>","</tr>"},{"<w:tc>","<td>"},{"</w:tc>","</td>"}},

RemoveList = List.Select(List.Transform({0..List.Count(Text.PositionOf(MyXML,"<",2))-1},each "<"&Text.BetweenDelimiters(MyXML,"<",">",_)&">"),each not List.Contains(List.Zip(ReplaceList){0},_)),

Result = List.RemoveLastN(Web.Page(List.Accumulate(List.Transform(RemoveList,each {_}&{""})&ReplaceList,MyXML,(s,c)=>Text.Replace(s,c{0},c{1})))[Data])

in try Result{index-1} otherwise Resultin docx