Excel Power Query功能大多數是UI介面不用寫函數
偶爾還是要自己寫函數, 以下列出比較常用的函數, 底線_, 表示所有欄位
* 我使用M365版本或Office2024, 舊版可能不支援某些函數
* 若power query右方沒顯示[查詢設定], 至上方[檢視表]內按[查詢設定]
換行符號: 字串都要用雙引號
"第一行#(cr)#(lf)第二行" 或是 "第一行#(lf)第二行"
數字轉文字,一般欄名只要加[...],但含符號或空格時, 要加[#"..."]:
Text.From([#"abc/def"])
篩選今天日期
= Table.SelectRows(上一步, each ([欄名] = DateTime.Date(DateTime.LocalNow())))
民國年y/m/d轉西元年:
按上方[新增自訂資料行]/[自訂資料行], 輸入
= Text.From(Int32.From(Text.BeforeDelimiter([民國日期],"/"))+1911) &"/"& Text.AfterDelimiter([民國日期],"/")
如果不想加欄位, 可參考下個公式
指定格式字串轉日期:
= Table.TransformColumns(上一步, {"Column3", each Date.FromText(_,[Format="dd/MM/yyyy"])})
判斷不同欄位轉換文字,可打Text"點"出一些函數(包含,開始於,結束於...):
按上方[新增自訂資料行]/[自訂資料行], 輸入
= if ([備註] = "C" or not Text.StartsWith([傳票編號], "1")) then "CC"
else if (Text.Contains([備註], "D") and [分行] = "001" ) then "DD"
else null
判斷同欄位直接轉換文字,可直接修改某欄位,
ex. Column3內容有SOLD就換成S, 否則換成B, 這裡的_指Column3:
= Table.TransformColumns(上一步, {"Column3", each if Text.Contains(_, "SOLD") then "S" else "B", type text})
希望可以在編輯時不要一直重載之前步驟資料
可加一步驟: buffer = Table.Buffer(上一步)
*注意: 如果要用.net或RPA呼叫的話, 不能使用此函數, 可能會導致卡死
by群組串接文字:
選擇所有要群組欄位,
按上方[常用]/[分組依據]
選[進階]可加入多個統計欄位
作業可選擇[所有資料列],即會引入所有欄位在同一欄位,在該欄位右上按倒三角可勾選想展開的欄位
如果想用"換行"串接多筆資料字串, 作業選擇[加總], 和要串接的欄位,
再修改公式內的List.Sum([串接的欄位])為Text.Combine([串接的欄位],"
")
by群組加索引:
選取要作群組的欄位,按上方[常用]/[分組依據], 按[確定]
將公式內的{"計數", each Table.RowCount(_), Int64.Type}
換成:
{"All", each Table.AddIndexColumn(_, "Index", 0, 1)}
即產生從0開始的索引[All]欄位內的欄位清單, (想從1開始就把0改成1)
再展開欄位即可
加入統計資料/串接多筆文字至原表:
假設[查詢設定]到第5步:[已新增自訂abc],在其按右鍵[在此步驟後插入步驟],作2次,
會長出第6步:[自訂2], 第7步[自訂1]
在[自訂2]中間Fx旁公式會是 = 已新增自訂abc
將公式改成 = Text.Combine((已新增自訂abc[要串的欄位]),"
")
或作加總= List.Sum(已新增自訂abc[金額])
2種作法:
a. 將原表格加新增欄位：
在[自訂1]中間Fx旁公式改成 = 已新增自訂abc
按上方[新增自訂資料行]/[自訂資料行], 輸入 = 自訂2
b. 接在原表格下方新增一列：
此時只會有一個值產生,至上方[轉換]/[到表格]/[到表格]
將欄位名改成與原表格欄位名相同,步驟會自動產生, 名為:[已重新命名資料行]
在[自訂1]中間Fx旁公式改成 = 已新增自訂abc
按上方[常用]/[附加查詢]
[要附加的資料表]選(目前)
將公式改成= Table.Combine({不用改, 已重新命名資料行})
依表格內的URL欄位去打api取得Json:
按上方[新增自訂資料行]/[自訂資料行], 輸入 = Json.Document(Web.Contents(Url網址))
傳資料會自動改用post = Web.Contents(Url網址, [Headers=[#"Content-Type"="application/json"], Content=Json.FromValue(Json.Document(Json字串))])
header要視情況修改, 也可能用"application/json-patch+json"
參考官網 :Web.Contents - PowerQuery M | Microsoft Learn
以參數作為來源時, 在不同電腦, 記得要在新電腦設定隱私權才能使用
刪除所有Column開頭欄位
有時excel會夾一些空的column, 欄位名會是Column開頭,可以建一個函數來刪掉那些Column
在左邊查詢上按右鍵/新增函數, 內容
= (CurrentTable as table) =>
let
// 抓取所有欄名
ColumnNames = Table.ColumnNames(CurrentTable),
// 過濾出自動命名的空白欄位
ColumnsToRemove = List.Select(ColumnNames, each Text.StartsWith(_, "Column")),
// 刪除這些欄位
Result = Table.RemoveColumns(CurrentTable, ColumnsToRemove)
in
Result
想把符合1之前和符合2開始的row都刪掉,是否包含符合row可在自訂1~2作+1或-1
假設到步驟10可以插入如下寫法:
//相等的寫法
自訂2 = List.PositionOfAny(步驟10[Column1], {"符合2"}),
//再計算的寫法
自訂1 = List.PositionOf(List.Transform(步驟10[Column1], each Text.StartsWith(_, "符合1")), true),
已保留第一個資料列 = Table.FirstN(步驟10,自訂2),
已移除頂端資料列 = Table.Skip(已保留第一個資料列,自訂1),
合併所有欄位
pdf載入常不如預期的切割,而且同一種格式的文件常切出不一樣欄位, 所以將所有欄位合併回去之後再自己切
可自訂函數如下
= (CurrentTable as table) =>
let
all = Table.AddColumn(CurrentTable, "合併欄位", each Text.Combine(Record.ToList(_), " ")),//用空1格作分隔符號
// 抓取所有欄名
ColumnNames = Table.ColumnNames(CurrentTable),
// 刪除這些欄位
Result = Table.RemoveColumns(all, ColumnNames)
in
Result
ps. 如果覺得excel切的欄位(ex. 它能區分27 NOV 2020是日期而放同一格)還有利用價值, 就用此pdf不會出現的字串(如 \ 或 /// )作分隔符號, 之後再用該字串作切割
載入各頁PDF時合併欄位
pdf載入時語法如下, Kind有自動切的Page和Table, 因為Table切得不準確,所以用Page:
let
來源 = Pdf.Tables(File.Contents("D:\…\…XXX.pdf"), [Implementation="1.3"]),
已篩選資料列 = Table.SelectRows(來源, each ([Kind] = "Page"))
in
已篩選資料列
這時呈現的欄位如下圖:
如果直接展開Data, 會使用第一頁切出的欄位, 這並不準確, 別頁切出更多的欄位就不會顯示,
所以可運用上一個函數合併欄位, 加入自訂:
= Table.AddColumn(上一步, "all", each 合併欄([Data]), type table)
再將all展開即可
pdf內的資料若很密集或是表格式, 導致分欄效果不好就不建議用powerquery
若遇到偶爾會將上下2格資料放在同一格時, 可以用換行符號作切割如下(有按鈕可用):
= Table.SplitColumn(上一步, "合併Column", Splitter.SplitTextByEachDelimiter({"#(lf)"}, QuoteStyle.Csv, true), {"Column1", "Column2"})
再視情況決定要怎麼處理Column2資料
[將標題升階]的函數在不同電腦會出現錯誤: 已將2個引數傳遞給必須有1個引數的函數
將整個語法拿出來, 把所有, [PromoteAllScalars=true]刪掉即可
同理有別的引數傳遞也可以刪減多長出來的參數, 最好的作法是將對方電腦EXCEL升版到比你高
文字遮罩: (規則同這篇[SQL] 遮罩)
(word as text) as text =>
let
首碼 = Lines.FromText(Text.Start(word,1)),
次到尾 = Text.Range(word,1),
切3字 = Splitter.SplitTextByRepeatedLengths(3)(次到尾),
遮首碼 = List.Transform(切3字, each (if Binary.Length(Text.ToBinary(Text.Start(_,1))) = Text.Length(Text.Start(_,1)) then "*" else "囗") & Text.Range(_, 1)),
result = Text.Combine(List.Combine({首碼, 遮首碼}))
in
result
呼叫方式
= Table.TransformColumns(上一步, {{"欄1", each Mask(_), type text}, {"欄2", each Mask(_), type text}})
動態操作欄位值:
原本都寫成 each [欄位名] 的寫法, 如果欄位名要以字串傳入, 可以寫成: Record.Field(_, "欄名")
對List跑迴圈動態加欄位:
tb就是某步驟Table, item是list的值, 以下範例為list是table的column清單
Record.Field可動態判斷某欄位值:
= List.Accumulate(某步驟list, 某步驟Table,
(tb, item) =>
Table.AddColumn(tb, _ &"測試", each if Record.Field(_, item) = null then null else "test")
)
也能動態將符合條件欄位設date類型:
= List.Accumulate(某步驟list, 某步驟Table,
(tb, columnName) =>
Table.TransformColumnTypes(tb, {columnName, type date}))
程式呼叫excel powerquery重新整理請參考:
[.Net] 使用Microsoft.Office.Interop.Excel 操作Excel載入, 更新和另存 ...
Taiwan is a country. 臺灣是我的國家