PowerQuery合併查詢詳解

一、Power Query合併查詢為縱向合併

Power Query能將兩個表按照某個公共列，如日期，國家等字段進行縱向合併，生成一個包含兩個表中所有字段的新表。操作步驟如下：

// 導入兩個表到Power Query編輯器
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    
    // 選擇“Start”列，即公共列，作為關聯字段
    MergeColumns = Table.NestedJoin(Source1,{"Start"},Source2,{"Start"},"NewColumn",JoinKind.LeftOuter),
    
    // 展開新列，生成新表
    ExpandMergedColumns = Table.ExpandTableColumn(MergeColumns, "NewColumn", {"Value"}, {"Table2"})
in
    ExpandMergedColumns

二、PowerQuery合併查詢錯誤

在進行Power Query合併查詢時，可能會出現錯誤，例如輸入的列名不存在，或者關聯的列數據類型不匹配。對於這些錯誤，Power Query會生成錯誤信息和堆棧跟蹤信息，方便用戶進行調試。

如果出現“值LimitedNumeric是沒有列Numeric”的錯誤，說明在合併查詢中，某個表中的列名發生了變化。解決方法是檢查所有表的列名，並保證它們一致。

// 模擬發生列名變化的情況
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2_ColumnRenamed"]}[Content],
    MergedColumns = Table.Join(Source1,"ID",Source2,"ID",JoinKind.LeftOuter),
in
    MergedColumns

三、PowerQuery合併查詢特別慢

當Power Query處理大量數據時，合併查詢操作可能會變得特別慢。這時，可以嘗試以下方法提高合併查詢的效率：

• 選擇Only Create Connection，只創建連接而不加載數據。
• 使用Reduce Date/Time操作，將日期和時間數據降低分辨率，減少數據量。
• 使用Group操作，對要合併的數據進行分組，提高合併效率。

// 使用Group操作提高Power Query合併效率
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Grouped1 = Table.Group(Source1, {"ID"}, {"Count", each Table.RowCount(_)}),
    Grouped2 = Table.Group(Source2, {"ID"}, {"Count", each Table.RowCount(_)}),
    MergedColumns = Table.Join(Grouped1,"ID",Grouped2,"ID",JoinKind.LeftOuter),
    ExpandedColumns = Table.ExpandTableColumn(MergedColumns, "Count", {"Count"}, {"Count"})
in
    ExpandedColumns

四、PowerQuery合併查詢數據出現兩次

在Power Query合併查詢中，可能會出現將所有數據重複的情況。原因在於在合併前未去重，導致數據重複。解決方法是使用Remove Duplicates操作，去除重複數據。

// 去除重複數據
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MergedColumns = Table.Join(Source1,"ID",Source2,"ID",JoinKind.LeftOuter),
    RemovedDuplicates = Table.Distinct(MergedColumns)
in
    RemovedDuplicates

五、PowerQuery合併查詢後數據重複

在Power Query合併查詢後，可能出現某個數據在輸出表中出現多次的情況。這是因為合併時，某個公共列中出現重複數據。解決方法是在合併前去重，再進行合併操作。

// 使用Distinct操作去除合併前即出現的重複數據
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    DistinctSource1 = Table.Distinct(Source1),
    DistinctSource2 = Table.Distinct(Source2),
    MergedColumns = Table.Join(DistinctSource1,"ID",DistinctSource2,"ID",JoinKind.LeftOuter)
in
    MergedColumns

六、Power Query合併查詢為縱向

Power Query也可以將兩個表按照行進行縱向合併，使得輸出表的列數增加。要實現這一操作，需要用到Append Queries功能。

// 縱向合併
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    AppendedQueries = Table.Combine({Source1, Source2})
in
    AppendedQueries

七、PowerQuery合併查詢模糊匹配

如果要在合併時進行模糊匹配，Power Query也能夠勝任。例如，在合併兩個表中的地址數據時，可以對地址進行模糊匹配，以避免因格式不同而導致不能正確合併的情況。要實現這一操作，需要用到Text.Combine和Text.FuzzyMatching功能。

// 模糊匹配
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    CombinedAddresses1 = Table.TransformColumns(Source1,{{"Address", Text.Combine, ", "}}),
    CombinedAddresses2 = Table.TransformColumns(Source2,{{"Address", Text.Combine, ", "}}),
    MergedColumns = Table.NestedJoin(CombinedAddresses1,{"City","State"},CombinedAddresses2,{"City","State"},
        "NewColumn",JoinKind.LeftOuter, null, [IgnoreCase=true, ComparisonKind=Text.Fuzzy])
in
    MergedColumns

總結

通過本文我們了解了Power Query合併查詢的基本概念和操作方法，完成了縱向和橫向合併查詢，並解決了常見的問題。同時，我們還學習了如何提高Power Query合併查詢的效率，並實現了模糊匹配。對於需要處理大量數據的人來說，Power Query合併查詢是一個強大而實用的工具。