☰

如何將 Excel 表格轉置為許多重複行中的唯一列？ （最好在 PowerQuery 中）

tag-icon • tag-icon microsoft-excel power-query
如何將 Excel 表格轉置為許多重複行中的唯一列？ （最好在 PowerQuery 中）

我到處尋找，並嘗試了很多，似乎大多數人不需要解決這個問題:-( 我有一個包含兩列的表。A 列（標籤）有 4 個不同的值，一遍又一遍地重複該模式。這些將成為我獨特的列名稱，而列B (VALUE) 具有這些標籤的符合值。

範例表：
來源表

現在我只想建立一個如下所示的表：（TAG 行中的每個不同值都成為列標題，VALUE 列中的所有值都成為這些新列下方的單獨值）

所需的目標表：
期望的目標表

當我匯入資料來源的 CSV 時，我最好已經在 PowerQuery 中完成所有這些轉置，但稍後在某些範圍/表操作中也可以這樣做。

任何幫助將非常感激 ：-）

答案1

在 Power Query 中，您可以透視 TAG 列，其中 VALUE 列作為值 和no aggregation。

但是，如果您僅對資料集執行此操作，則會傳回錯誤值，因為同一樞軸值有多個條目。

可以透過新增索引列來避免這種情況，該列會按順序對每個單獨的標籤條目進行編號。

為了做到這一點，並且還允許標籤不總是處於相同的順序，您可以

  • 按標籤分組
  • 為每個子表新增一個索引列
  • 展開表格
  • 以 TAG 為中心，不進行聚合
  • 刪除索引列

在下面的程式碼中，檢查註釋和應用步驟以更好地理解。

如果此方法在您的資料集上太慢，則可以發布一個自訂函數，該函數可以執行得更快。

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//Group By Tage
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TAG"}, {

        //add an Index column to each subtable
        {"with Index", each Table.AddIndexColumn(_,"Index",0,1), type table [TAG=nullable text, VALUE=nullable text, Index=Int64.Type]}}),

//Expand the sub tables
    #"Expanded with Index" = Table.ExpandTableColumn(#"Grouped Rows", "with Index", {"VALUE", "Index"}),

//Pivot with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Expanded with Index", List.Distinct(#"Expanded with Index"[TAG]), "TAG", "VALUE"),

//Remove the Index column
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

在此輸入影像描述

編輯以顯示用於執行非聚合樞軸的自訂函數

自訂功能
貼上到空白查詢中並按照程式碼註解中的方式重新命名

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

主要程式碼

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//call custom pivot function
    pivot = fnPivotAll(#"Changed Type","TAG","VALUE")
in
    pivot

相關內容