世界上第一个用powerbi处理族谱数据的例子

一个偶然的机会，让我把powerbi用到了开发者想破脑袋也不会想到的地方——用它来处理族谱（见下图）。

族谱.png

上图是族谱的一部分，基本结构就是一张世系图表和该世系的简要信息记录（第几世代，父亲是谁，生卒年、葬于何处，配偶及配偶所生的孩子）。

要求很简单，将族谱处理成类似于下图的形状：

处理过后的族谱

为什么要多次一举进行这样处理呢？我也不知道。只知道每个这样的族谱word 文件里有几百个这样的世系图及其简要信息，这样的word文档有二十多个。目标是把这二十多个word文件的中的世系图综合到一起，形成一张超大无比的世系图。

分析

要做出世系图来，首要的是获取到每个节点的父节点和子节点，也就是说，最后得到的数据文件应该类似于：

世系图数据结构

由于文件的最小结构是一张世系图和几行文字说明，并且从文字说明中可以轻松获得当前节点的名称、世系和父辈信息，所以，如果我们能搞定这部分内容，就能搞定所有内容。

由于所有族谱文件都是由这样的基本单元构成，所以我想到了用powerbi来提取这样的基本单元。

引入数据源到powerbi中

首先我们把20多个word文件合并成一个。要用到vba代码，我在这篇帖子里贴过一段网上找来的vba代码，可以直接使用。

Sub MergeDocs()
Dim rng As Range
Dim MainDoc As Document
Dim strFile As String, strFolder As String
With Application.FileDialog(msoFileDialogFolderPicker)
  .Title = "Pick folder"
  .AllowMultiSelect = False
  If .Show Then
    strFolder = .SelectedItems(1) & Application.PathSeparator
  Else
    Exit Sub
  End If
End With
Set MainDoc = Documents.Add
strFile = Dir$(strFolder & "*.doc") ' can change to .docx
Do Until strFile = ""
  Set rng = MainDoc.Range
  rng.Collapse wdCollapseEnd
  rng.InsertFile strFolder & strFile
  strFile = Dir$()
Loop
MsgBox ("Files are merged")
lbl_Exit:
Exit Sub
End Sub

无视缩进吧，反正可以运行。

怎么把合并后的word文件内容引入到powerbi中呢？很简单，复制粘贴到excel中即可。
然后用powerbi desktop导入该excel文件（也可以直接用excel206及以上版本直接导入数据源，据说powerbi desktop要快一点，所以我喜欢用它）。

用powerbi进行处理

先直接上代码：

  let
源 = Excel.Workbook(File.Contents("E:\族谱.xlsx"), null, true),//引入数据源
Sheet2_Sheet = 源{[Item="Sheet2",Kind="Sheet"]}[Data],
更改的类型 = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),//这一步是自动生成，可以不要
已添加索引 = Table.AddIndexColumn(更改的类型, "索引", 0, 1),//索引是按特定规律处理数据最基础的要素。
已添加自定义 = Table.AddColumn(已添加索引, "自定义", each if Text.End([Column1],2)="图表" then "图表" else if Value.Is(Number.FromText(Text.Start([Column1],2)),type number) then "有效" else null ),//这一步是为了找到世系图开始的位置，两个世系图之间，必然是一个完整的世系图文字描述。
已替换的错误 = Table.ReplaceErrorValues(已添加自定义, {{"自定义", null}}),//找不到“图表”二字的行用null填充
向下填充 = Table.FillDown(已替换的错误,{"自定义"}),
筛选的行 = Table.SelectRows(向下填充, each ([自定义] = "有效")),
删除的列 = Table.RemoveColumns(筛选的行,{"Column3", "Column4", "Column5"}),
已添加自定义1 = Table.AddColumn(删除的列, "世系", each Number.FromText(Text.Start([Column1],2))),
已替换的错误1 = Table.ReplaceErrorValues(已添加自定义1, {{"世系", null}}),
向下填充1 = Table.FillDown(已替换的错误1,{"世系"}),
已添加自定义2 = Table.AddColumn(向下填充1, "包含世", each if Text.At([Column1],2)="世" then [Column2] else null),//把每一个节点单独提取出来
删除的列1 = Table.RemoveColumns(已添加自定义2,{"索引"}),
已添加索引1 = Table.AddIndexColumn(删除的列1, "索引", 0, 1),
向下填充2 = Table.FillDown(已添加索引1,{"包含世"}),
已添加自定义3 = Table.AddColumn(向下填充2, "父辈",each  向下填充2[Column1]{[索引]+1}),//这一步很关键，每个节点的父辈信息在下一行，我们把它提取到当前行
分组的行 = Table.Group(已添加自定义3, {"包含世"}, {{"计数", each Table.RowCount(_), type number}, {"all", each _, type table [Column1=text, Column2=text, 自定义=text, 世系=number, 包含世=text, 索引=number, 父辈=text]}}),
自定义1 = Table.TransformColumns(分组的行,{"all",each Table.FirstN(_,1)}),
 展开 = Table.ExpandTableColumn(自定义1, "all", {"世系", "父辈"}, {"世系", "父辈"}),
按分隔符拆分列 = Table.SplitColumn(展开, "父辈", Splitter.SplitTextByEachDelimiter({"公"}, QuoteStyle.Csv, false), {"父辈.1", "父辈.2"}),//获取父辈
更改的类型1 = Table.TransformColumnTypes(按分隔符拆分列,{{"父辈.1", type text}, {"父辈.2", type text}}),
删除的列2 = Table.RemoveColumns(更改的类型1,{"父辈.2"}),
重命名的列 = Table.RenameColumns(删除的列2,{{"父辈.1", "父辈"}})
in
  重命名的列