[[20210903133523]]　『パワークエリを使って累計の表を累計でない表に整』（豆腐とキムチ）　ページの最後に飛ぶ

『パワークエリを使って累計の表を累計でない表に整形したい』（豆腐とキムチ）

初めまして。どうぞよろしくお願いします。
パワークエリを使って、以下の様な累計になっている表を

◆車販売台数(累計)
　　田中　木村　鈴木
4月　1台　2台　4台
5月　2台　3台　6台
6月　4台　6台　7台

ポビット解除を使って以下の様に整形していますが

◆車販売台数(累計)
4月　田中　1台
4月　木村　2台
4月　鈴木　4台

5月　田中　2台
5月　木村　3台
5月　鈴木　6台

6月　田中　4台
6月　木村　6台
6月　鈴木　7台

ここから更に以下の様な累計でない表に
整形することは可能でしょうか？

◆車販売台数(累計でない)
4月　田中　1台
4月　木村　2台
4月　鈴木　4台

5月　田中　1台
5月　木村　1台
5月　鈴木　2台

6月　田中　2台
6月　木村　3台
6月　鈴木　1台

今までは手作業で関数を使って累計でない数字を出してから
パワークエリを使って表を整形していましたが
もしパワークエリを使って出来るならとても便利だなと思いました。
お分かりになる方いらっしゃいましたら
どうぞご教授ください。よろしくお願いします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

0始まりのインデックス列と１始まりのインデックス列を追加し
クエリのマージを実行してはどうでしょうか。

（マナ） 2021/09/03(金) 17:05

マナ様、早速のご返信をどうもありがとうございます！
なるほど！インデックス列というものを使えば良いんですね！
ありがとうございます！

本を見ながら試行錯誤でやってみたら何となくですが形にすることが出来ました！
ちなみに私のやった操作ですが…

◆車販売台数(累計)
　　田中　木村　鈴木
4月　1台　2台　4台
5月　2台　3台　6台
6月　4台　6台　7台

1.上の表を【データを取得】で読み込む際に【列の追加→インデックス列→1から】で読み込む

2.また同じ表を【データを取得】で読み込む際に【列の追加→インデックス列→0から】で読み込む

3.【クエリのマージ】で1と2の表をインデックス列をキーにしてマージを実行する

4.【カスタム列の追加】で"1の田中列"－"2の田中列"で差分を出す

とやってみたのですが、マナ様が教えてくださったことは
この理解でよろしかったでしょうか？

あと、すみません。表は簡略化して田中、木村、鈴木の3人しか書いていませんが
実はあと700人ほどいます。
私のやったやり方だと4の【カスタム列の追加】をあと700回繰り返さないといけないのですが
エクセルのフィルコピーの様に簡単に同じ処理を記述する事は可能でしょうか？

たくさん質問してしまい大変申し訳ありません。
ご教授して頂けたら有り難いです。どうぞよろしくお願いします。

（豆腐とキムチ） 2021/09/03(金) 18:44

ピボット解除するとよいです。

 Power Queryエディターを起動し、
 詳細エディターにコピペして確認してみてください。

 なお、テーブル名は、テーブル1で
 1列目の見出しを、販売月としています。

 >　　     田中　木村　鈴木
      ↓
 　販売月　田中　木村　鈴木

 '------
 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    追加されたインデックス = Table.AddIndexColumn(ソース, "インデックス", 0, 1, Int64.Type),
    追加されたインデックス1 = Table.AddIndexColumn(追加されたインデックス, "インデックス.1", 1, 1, Int64.Type),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス1, {"販売月", "インデックス", "インデックス.1"}, "属性", "値"),
    #"名前が変更された列 " = Table.RenameColumns(ピボット解除された他の列,{{"属性", "名前"}, {"値", "累積販売台数"}}),
    マージされたクエリ数 = Table.NestedJoin(#"名前が変更された列 ", {"インデックス", "名前"}, #"名前が変更された列 ", {"インデックス.1", "名前"}, "名前が変更された列 ", JoinKind.LeftOuter),
    #"展開された 名前が変更された列 " = Table.ExpandTableColumn(マージされたクエリ数, "名前が変更された列 ", {"累積販売台数"}, {"累積販売台数.1"}),
    置き換えられた値 = Table.ReplaceValue(#"展開された 名前が変更された列 ",null,0,Replacer.ReplaceValue,{"累積販売台数.1"}),
    挿入された引き算 = Table.AddColumn(置き換えられた値, "減算", each [累積販売台数] - [累積販売台数.1], type number),
    #"名前が変更された列 1" = Table.RenameColumns(挿入された引き算,{{"減算", "販売台数"}}),
    削除された他の列 = Table.SelectColumns(#"名前が変更された列 1",{"販売月", "名前", "販売台数"}),
    並べ替えられた行 = Table.Sort(削除された他の列,{{"販売月", Order.Ascending}, {"名前", Order.Ascending}})
 in
    並べ替えられた行

（マナ） 2021/09/03(金) 19:08

↑最後は、「並べ替え」でなく、「列のピボット」でしたか？

（マナ） 2021/09/03(金) 19:22

マナ様　たくさんのアドバイスをどうもありがとうございます！
レベルが高すぎてマナ様が教えてくださったことがまるで呪文の様で理解が全く出来ていませんでしたが
詳細エディターにコピペして【適用したステップ】でひとつひとつのの動作を確認してるうちに
段々とどの様な処理がなされているのか理解できそうな感じです！！！
本当にどうもありがとうございます！
おかげさまで今までずっと苦労していた業務から解放されそうです！
パワークエリって素晴らしい技術ですね！もっと色々と知ってみたいと思いました！
マナ様のように詳しくなりたいと思ったのですがおすすめの書籍などありましたら是非教えてください！

あ、あと最後は「並べ替えられた行」で問題ありませんでした。本当にどうもありがとうございました！
（豆腐とキムチ） 2021/09/03(金) 20:37

すみません。上記のファイルを参考書を読みつつ
一応作ってみたのですが、また分からない事が起きたので
どなたかご教示ください。

0始まりのインデックスと1始まりのインデックスを追加し
クエリのマージを実行し減算を行おうとしているのですが
毎回、一箇所、インデックスの順番がおかしくなります。

インデックスが1273,1274,【1282】,1275,1276,1277,1278,1279,1280,1281
と言った具合に1番最後に来るはずの番号がなぜか8個くらい前に
きてしまいます。

ちゃんと確かめていないのですがクエリを作って
1番最初に元ファイルを読み込む時はうまくいくのですが
元ファイルを更新して次回、読み込む時になると同様の現象が起きてしまうようです。

どなたか原因がわかりますでしょうか？よろしくお願い致します
（豆腐とキムチ） 2021/10/05(火) 16:57

マージする前のステップでは問題ないのですか？

（マナ） 2021/10/05(火) 17:33

マナ様、毎度教えて頂きありがとうございます。
マージする前のステップを今一度、確認してみます！
（豆腐とキムチ） 2021/10/05(火) 18:30

解決しました！マナ様からアドバイスをいただき
マージする前のステップを確認しましたところ
便宜的、社員コードを付加する必要があり
行列の入れ替えをした後にインデックス列を追加していたのですが

◆車販売台数(累計)
　　田中　木村　鈴木
4月　1台　2台　4台
5月　2台　3台　6台
6月　4台　6台　7台

　　　↓↓↓

　　　4月　5月　6月　0
田中　1台　2台　4台　1
木村　2台　3台　6台　2
鈴木　4台　6台　7台　3

?@作業しやすいようにインデックス列を先頭に移動

　　　↓↓↓

0 4月　5月　6月
1 田中　1台　2台　4台
2 木村　2台　3台　6台
3 鈴木　4台　6台　7台

?Aインデックス列と社員名の列を結合した後、行列の入れ替え

  0　1田中   2木村   3鈴木 
4月　1台　　2台　　4台 
5月　2台　　3台　　6台 
6月　4台　　6台　　7台

?Bこの後、減算するためのインデックス列を追加していったんですが
?@の社員コードのインデックス列を移動したのが失敗でした。
列の移動をしてしまうと最初のデータを読み込む際に列を固定してしまい
更新されたデータを読み込む際、インデックスが不連続になってしまっていました
列の移動をせずに列の結合を実行したらうまく行きました！

マナ様、今回もどうもありがとうございました！

（豆腐とキムチ） 2021/10/06(水) 18:18

コメント返信：

[ 一覧(最新更新順) ]

[[20210903133523]] 『パワークエリを使って累計の表を累計でない表に整』（豆腐とキムチ） ページの最後に飛ぶ

[[20210903133523]]　『パワークエリを使って累計の表を累計でない表に整』（豆腐とキムチ）　ページの最後に飛ぶ