☰

Excel テーブルを多数の重複行から一意の列に転置するにはどうすればよいですか? (PowerQuery で可能)

tag-icon • tag-icon microsoft-excel power-query
Excel テーブルを多数の重複行から一意の列に転置するにはどうすればよいですか? (PowerQuery で可能)

あらゆる場所を調べて、いろいろ試しましたが、どうやらほとんどの人はこの問題を解決する必要はないようです :-( 2 つの列があるテーブルがあります。列 A (タグ) には 4 つの異なる値があり、そのパターンが何度も繰り返されます。これらが私の個別の列名になります。列 B (値) には、それらのタグに一致する値があります。

例の表:
ソーステーブル

ここで、次のようなテーブルを作成したいと思います。(TAG 行の各個別値は列ヘッダーになり、VALUE 列のすべての値は新しい列の下の個別の値になります)

希望するターゲットテーブル:
希望するターゲットテーブル

できれば、データの元となった CSV をインポートするときに、この転置をすべて PowerQuery で実行しておくのが望ましいのですが、後から範囲/テーブル操作でこれを行うことも問題ありません。

どのような助けでも本当にありがたいです :-)

答え1

Power Query では、VALUE 列を値として、TAG 列をピボットします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

関連情報