Excel Power Query功能大多數是UI介面不用寫函數
Excel Power Query功能大多數是UI介面不用寫函數,
偶爾還是要自己寫函數, 以下列出比較常用的函數:
數字轉文字,一般欄名只要加[...],但含符號時, 要加[#"..."]:
Text.From([#"abc/def"])
篩選今天日期
= Table.SelectRows(上一步, each ([欄名] = DateTime.Date(DateTime.LocalNow())))
民國年y/m/d轉西元年:
按上方[新增自訂資料行]/[自訂資料行], 輸入
= Text.From(Int32.From(Text.BeforeDelimiter([民國日期],"/"))+1911) &"/"& Text.AfterDelimiter([民國日期],"/")
判斷不同欄位轉換資料,可打Text"點"出一些函數(包含,開始於,結束於...):
按上方[新增自訂資料行]/[自訂資料行], 輸入
= if ([備註] = "C" or not Text.StartsWith([傳票編號], "1")) then "CC"
else if (Text.Contains([備註], "D") and [分行] = "001" ) then "DD"
else null
希望可以在編輯時不要一直重載之前步驟資料
可加一步驟: buffer = Table.Buffer(前步驟)
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欄位名]))
刪除所有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(buffer[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(_), " ")),
// 抓取所有欄名
ColumnNames = Table.ColumnNames(CurrentTable),
// 刪除這些欄位
Result = Table.RemoveColumns(all, ColumnNames)
in
Result
Taiwan is a country. 臺灣是我的國家