152-技巧-Power Query 快速合併文件夾中表格之自定義函數 TableXlsxCsv
- 2022 年 5 月 21 日
- 筆記
- power bi, power query
152-技巧-Power Query 快速合併文件夾中表格之自定義函數 TableXlsxCsv
附件下載地址：//jiaopengzi.com/2602.html
一、背景
在我們使用 Power BI 或者 Power Pivot 做數據分析模型時，使用 Power Query 做數導入，經常會遇到如下場景：
- 同一文件夾下多個表格的合併。
- 同一個 Workbook 中有多個相同的欄位的 WorkSheet 。
- Xlsx, Xls, Csv 並存。
- 欄位的類型更改每次都要打開 Power Query 去修改。
- 欄位增加和刪除每次都要打開 Power Query 去修改。
- 欄位重命名後要修改類型等多處操作。
- 合併的表格表頭有多行不需要的資訊，影響數據的讀取。
- 有多個項目文件夾都是合併表格，要重複寫 M 語句或者重複面板操作。
- 把文件名稱按照規則添加列到對應的合併的表格里。
- Excel 文件篩選後，會出現數據變多的情況。
基於上述的場景，我們寫好了一個能應對的自定義函數：TableXlsxCsv ，讓我們來看一下函數的介紹。
這個函數的提示和 Power Query 內置函數的提示是一樣的，基本函數的使用和說明都清晰了。
二、TableXlsxCsv 的使用方法
1、參數介紹
Ⅰ、必填參數
1參:FolderName
data 文件夾下需要合併表的文件夾名稱，比如demo中的文件夾：01_訂單
2參:RemoveFirstRows
數據從頂部開始需要移除的行數，一般情況需要把標題及以前的數據都要移除，在名稱管理中已經管理好了對應的標題。
3參:RemoveLastRows
數據從底部開始需要移除的行數，比如底部有類似匯總行的數據。
4參:IsAddColumn
是否需要把文件名稱按照規則添加列，只能填寫 true 或者 false 兩個參數，true 表示需要添加列，false 表示不需要添加列。
注意：當 4 參 IsAddColumn 為 false，後面四個參數不用填寫，為 true 時，後面四個參數必須填寫。
Ⅱ、可選參數
5參:ColunmName
當 4 參 IsAddColumn 為 true 表示需要把文件名稱按照規則添加列，ColunmName 即為新增列的名稱，注意不要與合併表中的欄位名稱重複。
6參:ColumnType
5 參 ColunmName 添加列的數據類型，只能填寫如下類型：
type text
type number
Int64.Type
type date
type datetime
type time
7參:NameStartNumber
文件名稱字元開始的索引，比如文件名稱：訂單資訊-2022-05-16；索引是從 0 開始的，需要取年與日的話索引的開始就是 5 。
8參:Length
文件名稱在 6 參 NameStartNumber 後的長度，比如文件名稱：訂單資訊-2022-05-16；索引是從 0 開始的，需要取年與日的話索引的開始就是 5 ，Length 就是 2022-05-16 的長度 10 個字元。
2、必要配置
Ⅰ、項目文件夾
項目新建一個文件夾，名稱隨意，我們當前的案例項目文件夾：demo152
Ⅱ、數據文件夾 data
項目文件夾建立好後，需要把項目文件和數據文件區分開，我們建立 data 文件夾來管理數據，當然這個文件夾的名稱也是隨意，根據自己的需求來即可。
Ⅲ、數據欄位的名稱管理
在 數據文件夾 data 下有一個 00_輔助表 的文件夾，這個文件名稱不能更改，是和函數綁定的名稱。
Ⅳ、需合併表格的名稱管理
在輔助表文件夾中，有一個 Excel 文件： 01_名稱管理，這個文件就是把需要合併的文件夾中的表格的重命名、數據類型、是否顯示等都解耦出來，在這個 Excel 文件中操作即可。
具體配置如下圖：
1、文件夾：表示需要合併的文件夾，比如當前的：01_訂單 文件夾。
2、ID：表示需要合併表格從左開始的欄位序號，序號從1開始依次遞增，新增加的文件夾需要再次從 1 開始。
3、原始名稱：表示需要合併表格從左開始的欄位名稱。
4、統一名稱：表示在原始名稱的基礎下重命名後的欄位名稱，比如當前的配置就把所有欄位都重命名了。
5、欄位類型ID：表示表格合併後最後統一數據類型標識，具體對應參照左邊表格。
6、是否顯示：表示該欄位是否在最終上載到模型中，只能填寫：**是 ** 或者 否，填寫其它會報錯。
Ⅴ、Power Query 路徑配置
打開項目文件把路徑參數：Path 配置好，注意這個參數就是 前面 data 文件夾的路徑。參數 Path 名稱不能更改,，注意大小寫。
Ⅵ、配置自定義函數 TableXlsxCsv
在 Path 下面 新建一個空白查詢，點擊高級編輯器，把 TableXlsxCsv 的 M 程式碼複製到編輯框裡面保存，重命名查詢為 TableXlsxCsv即可。
TableXlsxCsv 的 M 程式碼：
let
fx0 = (
FolderName as text,
RemoveFirstRows as number,
RemoveLastRows as number,
IsAddColumn as logical,
optional ColunmName as text,
optional ColumnType as text,
optional NameStartNumber as number,
optional Length as number
) as table =>
let
Types0 = List.Buffer({type text, type number, Int64.Type, type date, type datetime, type time}),
Types1 = List.Buffer({"文本", "小數", "整數", "日期", "日期時間", "時間"}),
ColumnNameTable0 = Table.Buffer( Excel.Workbook(File.Contents(Path & "\00_輔助表\01_名稱管理.xlsx"), true, true){[Item = "ColumnNameTable", Kind = "Table"]}[Data]),
ColumnNameTable0Fileds = List.Buffer(Table.ColumnNames(ColumnNameTable0)),
TypeListNewjpz = List.Buffer(List.Zip({ColumnNameTable0Fileds, List.Transform({0, 2, 0, 0, 2, 0}, each Types0{_})})),
ColumnNameTable1 = Table.Buffer(Table.TransformColumnTypes(ColumnNameTable0, TypeListNewjpz)),
ColumnNameTable2 = Table.Buffer(Table.SelectRows(ColumnNameTable1, each ([文件夾] = FolderName))),
ColumnCount = Table.RowCount(ColumnNameTable2),
ColumnNameTableX = Table.Buffer(Table.SelectRows(ColumnNameTable2, each [是否顯示] = "是")),
ColumnNameListOld = List.Buffer(List.Transform(ColumnNameTableX[ID], each "Column" & Text.From(_))),
ColumnNameListNew = List.Buffer(List.Zip({ColumnNameListOld, ColumnNameTableX[統一名稱]})),
TypeListNew = List.Buffer(List.Zip({ColumnNameTableX[統一名稱], List.Transform(ColumnNameTableX[欄位類型ID], each Types0{_})})),
FileList0 = Table.Buffer(Folder.Files(Path & "\" & FolderName)),
BinaryList0 = List.Buffer(FileList0[Content]),
List0 = List.Buffer({0 .. List.Count(BinaryList0) - 1}),
Extension0 = List.Buffer(FileList0[Extension]),
NameList0 = List.Buffer(FileList0[Name]),
NameList1 = List.Transform(NameList0, each Text.Middle(_, NameStartNumber, Length)),
TableList0 = List.Buffer(
List.Transform(
List0,
(n) =>
let
wb = Table.SelectColumns(
if Text.Contains(Extension0{n}, "Csv", Comparer.OrdinalIgnoreCase) then
Table.RemoveLastN(
Table.RemoveFirstN(
Csv.Document(BinaryList0{n}, [Delimiter = ",", Columns = ColumnCount]),
RemoveFirstRows
),
RemoveLastRows
)
else
Table.Combine(
List.Transform(
Table.SelectRows(
Excel.Workbook(BinaryList0{n}, null, true),
each not Text.Contains([Name], "Filter", Comparer.OrdinalIgnoreCase)
)[Data],
each Table.RemoveLastN(Table.RemoveFirstN(_, RemoveFirstRows), RemoveLastRows)
)
),
ColumnNameListOld
)
in
if IsAddColumn then Table.AddColumn(wb, ColunmName, each NameList1{n}) else wb
)
),
Rename0 = Table.Buffer(Table.RenameColumns(Table.Combine(TableList0), ColumnNameListNew)),
Result =
if IsAddColumn then
Table.TransformColumnTypes(
Rename0,
TypeListNew & {{ColunmName, Types0{List.PositionOf(Types1, ColumnType)}}}
)
else
Table.TransformColumnTypes(Rename0, TypeListNew)
in
Result,
TypeMeta0 = type function (
FolderName as (
type text
meta [
Documentation.FieldCaption = "1參:FolderName, Path 路徑下需要合併表的文件夾名稱.",
Documentation.SampleValues = {"訂單資訊"},
Documentation.AllowedValues = List.RemoveItems(
List.Transform(
List.Distinct(Folder.Files(Path)[Folder Path]),
each Replacer.ReplaceText(Replacer.ReplaceText(_, Path, ""), "\", "")
),
{"00_輔助表"}
)
]
),
RemoveFirstRows as (
type number
meta [
Documentation.FieldCaption = "2參:RemoveFirstRows,從頂部開始移除的行數.",
Documentation.SampleValues = {1},
Documentation.AllowedValues = {0 .. 50}
]
),
RemoveLastRows as (
type number
meta [
Documentation.FieldCaption = "3參:RemoveLastRows,從底部開始移除的行數.",
Documentation.SampleValues = {0},
Documentation.AllowedValues = {0 .. 50}
]
),
IsAddColumn as (
type logical
meta [
Documentation.FieldCaption = "4參:IsAddColumn,是否需要把文件名稱按照規則添加列.",
Documentation.SampleValues = {false},
Documentation.AllowedValues = {false, true}
]
),
optional ColunmName as (
type text
meta [
Documentation.FieldCaption = "5參:ColunmName,添加列的名稱.",
Documentation.SampleValues = {"NewName"}
]
),
optional ColumnType as (
type text
meta [
Documentation.FieldCaption = "6參:ColumnType,添加列的類型.",
Documentation.SampleValues = {"文本"},
Documentation.AllowedValues = {"文本", "小數", "整數", "日期", "日期時間", "時間"}
]
),
optional NameStartNumber as (
type number
meta [
Documentation.FieldCaption = "7參:NameStartNumber,文件名稱字元開始的索引.",
Documentation.SampleValues = {0},
Documentation.AllowedValues = {1 .. 50}
]
),
optional Length as (
type number
meta [
Documentation.FieldCaption = "8參:Length,文件名稱在 NameStartNumber 後的長度.",
Documentation.SampleValues = {1},
Documentation.AllowedValues = {1 .. 50}
]
)
) as table
meta [
Documentation.Name = "TableXlsxCsv",
Documentation.LongDescription
= "返回同一文件夾下相同欄位表格合併後的表格,兼容 XLSX, XLS, CSV 三種常見的格式, 同時兼容一個 WorkBook 多個相同欄位的 WorkSheet 的 Excel 文件.【注意】:當參數 IsAddColumn 為 true 時,後四個參數 ColunmName, TypeNumber, NameStartNumber, Length 必填; 【00_輔助表】文件夾下的名稱管理 Excel 文件需要在使用 TableXlsxCsv 提前配置好.",
Documentation.WhoAskedTheRightQuestion = "www.jiaopengzi.com",
Documentation.Author = "焦棚子",
Documentation.Examples = {
[
Description = "1、從文件夾【訂單資訊】中合併表格,且不用文件名稱添加列.",
Code = "TableXlsxCsv( " & """訂單資訊""" & ", 1, 0, false )",
Result = "當參數 IsAddColumn 為 false 時,後四個參數可以不寫;從文件夾【訂單資訊】中每個表格從頂部移除 1 行後合併表,且不用文件名稱添加列."
],
[
Description = "2、從文件夾【訂單資訊】中合併表格,使用文件名稱按照規則添加列.",
Code = "TableXlsxCsv( " & """訂單資訊""" & ", 1, 3, true," & """數據日期""" & ", "&"""日期"""&", 12, 10 )",
Result
= "當參數 IsAddColumn 為 true 時,後四個參數必填;從文件夾【訂單資訊】中每個表格從頂部移除 1 行並且從底部移除 3 行後合併表,添加列的名稱:數據日期,數據類型為:日期格式,按照文件名稱從 12 個字元開始取 10 個字元."
]
}
],
fx1 = Value.ReplaceType(fx0, TypeMeta0)
in
fx1
M 程式碼複製到高級編輯器後保存後，必要配置就算完畢了。
3、TableXlsxCsv 調用
Ⅰ、數據源
首先來看一下數據源，在函數說明中介紹到，我們的自定義函數 TableXlsxCsv ，可以兼容 Xlsx、Xls、Csv 三種常見的本地文件，我們直接通過一個函數就可以解決了，不用去考慮表格是否篩選，表頭有多行，同一個 Workbook 有多個相同的 WorkSheet等問題了。
在 data 文件夾下新建文件夾來管理同一欄位表格，案例中文件夾是：01_訂單，在前面配置名稱管理時也可以看。
細心的朋友發現了，我們這個文件夾下是有三種數據類型。
我再看下具體的每個文件下的內容：
訂單資訊-2022-03-15 文件類型是 Xlsx，裡面有 2 個Sheet 分別對應 15 號的數據和 16 號的數據，為了方便演示 15 號的 Sheet 中有 5 行數據， 16 號的 Sheet 中有 6 行數據。
訂單資訊-2022-03-17 文件類型是 Xls，裡面只有 1 個Sheet 為了方便演示 17 號的文件中有 7 行數據。
訂單資訊-2022-03-15 文件類型是 Csv，為了方便演示 18 號的文件中有 8 行數據。
Ⅱ、函數調用
A、不添加列
按照圖示輸入資訊後點擊調用函數，即可得到新的查詢，更改查詢名稱為自己需要的名稱即可。
我們看到查詢語句非常簡單了，而且可以復用。
let
源 = TableXlsxCsv("01_訂單", 1, 0, false )
in
源
B、添加列
按照圖示輸入資訊後點擊調用函數，即可得到新的查詢，更改查詢名稱為自己需要的名稱即可。
注意：在需要添加列的情況下，後四個參數都是必填。
M 語句如下：
let
源 = TableXlsxCsv("01_訂單", 1, 0, true, "數據日期", type date, 5, 10)
in
源
C、結果對比
- 結合前面的數據源，可以看到 Xlsx, Xls, Csv 在同一個文件夾中是可以通過一個我們寫的自定義函數合併的。雖然我們做了三個中格式兼容，但從效率來講我們更希望看到的是 Csv 格式。導出的平面文件 PQ 讀取的效率高低順序： Csv > Xlsx > Xls ，盡量不使用Xls， Xls 這種格式讀取是比較慢。
- 看到下單日期，3 月 15 號和 3 月 16 號的數據在同一個 Workbook 的不同的 Sheet 裡面，也是合併到了一起。
- 篩選的零時表也是剔除掉的。
- 每個欄位的名稱按照輔助表裡名稱管理的 Excel 配置的來重命名的。
- 每個欄位的格式按照輔助表裡名稱管理的 Excel 配置的數據類型來設置的。
- 每個欄位的顯示按照輔助表裡名稱管理的 Excel 配置欄位是否顯示來顯示的，如自動編號不顯示，其它都顯示出來了。
- 下圖第一張是沒有後四個參數的，也就沒有添加列，第二張圖片有後四個參數，在數據後添加了一列。需要注意的我的文件名稱的格式：訂單資訊-2022-03-15 這個數據名稱也是有將就的，年是四位數佔位，月是和日都必須是兩位數佔位，比如這裡的 3 月，就是 03 ;這裡的規則其實使用的函數 Text.Middle 這個規則可以自己去修改。
- 最後的添加列，是對文件名稱使用規則，而不是 Sheet 名稱，比如這裡的訂單資訊-2022-03-15 ，其中有兩個 Sheet，最後添加列也是 3 月 15 號。
- 第二張第 4 參數為 2，每個表在合併前都移除了最後兩行，可以和上述數據源作對比。
三、總結
1、我們自定義的這個函數，其實就是封裝了 Excel.Workbook 和 Csv.Document 兩個主要的函數。
2、當然如果自己寫一個這樣的函數的成本是很高的，我們寫好的函數可以直接拿來用即可。
3、有了這函數以後，我們在做項目的時候就提高數據導入的效率，同時一張 Excel 來管理我們的數據欄位。
4、有朋友可能會說直接使用資料庫不更方便嘛，使用資料庫也是完全可行的，但是更多的業務場景給的就是 Excel 、Csv 等文件，使用這個 PQ 自定義函數會更方便快捷。
5、這個函數的復用還體現在，我們的項目會多張，這樣就有多個文件夾，我們通過名稱管理的 Excel 文件，把需要的表格名稱管理起來，同時建立好對應的文件夾，這樣我們就可以直接快速的合併文件夾裡面的內容了。比如如這樣：
6、最後需要說明的一點，使用自定義函數合併的表格一定要自己校驗。
7、附件中有詳細的分解步驟，有興趣的可以研究下。
by 焦棚子
