タグ別アーカイブ: Power Query
上級編1回目サンプルPDFファイル
中級編9回目サンプル
グループ別に合計や平均に加えて標準偏差を抽出する方法
【数字は平均だけではよく分かりません。グループ化機能を更に深堀し、分析用の列に標準偏差も加えましょう！】
グループ化機能はとても便利です！グループ毎の集計を簡単に行いつつ、集計した数値を並べることができます
但し、グループ化の集計メニューには「標準偏差」が足りません
平均値を集計しても、バラツキが大きいのか、バラツキが小さいのかで平均値の解釈が違ってきます
例えば、上のグラフにはA～Cという３つのグループがあります
いずれも平均は１５０です
ただ明らかにグループBとグループCでは平均値１５０の解釈が違ってきますよね
今回はグループ化のラインナップに標準偏差をサクッと加える方法を解説します
グループ化の基本
グループ化を行う際、「基本」ではなく「詳細」を選択すれば複数の集計を行うことができます
今回は「合計」「人数」「平均」を集計した後に「すべての行」を集計に加え、後で集計結果を標準偏差に集計し直します
すべての行によるグループ化
「すべての行」のグループ化により、グループ別にテーブルが作成されます
この各テーブルが配置された列を使用してカスタム列を作成します
カスタム列内では「List.StandardDeviation」というM関数を使用します
使用できる列には「標準偏差」を集計する対象の「点数」の列がありません
ただ「標準偏差」の列は各テーブルの集合ですので、⇒標準偏差列⇒テーブル内の「点数」という流れで列を指定します
上の画像の「点数列」は手動で角括弧：[を使用して作成します
これでグループ別に標準偏差が集計されます
＜まとめ＞
今回はグループ別に標準偏差を集計する方法を解説しました
グループ化/すべての行とM関数の組み合わせにより、簡単に標準偏差を集計することができます
ワークシート関数と違い、グループ別に分けて集計する必要もなく、ピボットテーブルと違って直接テーブル化を行えるのでとても便利です
今後もPower Queryの便利術を発信していきます
【パワークエリ・チャレンジ】グループ別の小計と合計を表示する
【通常は一律で処理するPower Queryですが、M関数を使えば小計と合計を列に追加・挿入することができます】
M関数をうまく活用することで、通常では考えられないような表を作成することができます
今回は上のGIF画像の左の表から、グループ別の小計と全体の合計を自動的に挿入します
上記の内容ではＭ関数を使用しますが、その前に大事なのはレコードやテーブルとは何か？どのように作成できるか？ということです
まずは、空のクエリからレコードとテーブルを作成してみます
レコードとテーブルの作成
レコード
レコードは角括弧：[]を使用することで空のクエリから作成することができます
テーブル
こちらはM関数：#tableを使用します
関数内では波括弧：{}を組み合わせて、列名と行の値を指定します
行の値は２重の波括弧:{{}}で指定する点にご注目ください
グループ別の小計の挿入
まずは、小計を行う単位：部門でグループ化を行います
操作は「すべての行」を指定します
すると数式バーの中身は次のようになっています
= Table.Group(ソース, {“部門”}, {{“ALL”, each _, type table [部門=text, 商品=text, 金額=number]}})
こちらの数式バーを修正する形で小計を追加します
まず,type tableから]の部分は列の形式を指定しているだけなので、今回は消去しておきます
消去すると次の形となります
= Table.Group(ソース, {“部門”}, {{“ALL”, each _}})
上記の数式の中で「each _」は各テーブルの中身・各行を借り受けしています
こちらに各小計を追加します
小計はテーブルを追加する形で行います
追加なので＆からはじめます
= Table.Group(ソース, {“部門”}, {{“ALL”, each _ & }})
&に続くのは、前述のM関数#tableになります
= Table.Group(ソース, {“部門”}, {{“ALL”, each _ & #table(Table.ColumnNames(_),}})
今回は列名を指定するのに波括弧：{}を使用ぜず、Table.ColumnNames関数を使用します
Table.ColumnNames関数を使用することで、列名のリスト：{“部門”,”商品”,”金額”}を代用して指定します
次にテーブルの行の値①②③を指定し、#table関数の右括弧)を付け加えます
= Table.Group(ソース, {“部門”}, {{“ALL”, each _ & #table(Table.ColumnNames(_),{{①[部門]{0}&”小計”,②””,③List.Sum([金額])}})}})
①は部門列に対応します
{0}と指定することで、各テーブルの部門列の１行目を指定しつつ、”小計”を組み合わせることで「部門名＋小計」の文字列を追加します
②は空欄を指定します
③は各テーブルの金額列の小計を指定します
ちなみに上記の数式の右から２番目の）が#table関数の）です
これで小計が追加できました
後は展開処理を行い、ステップ名を「mySubTotal」とします
合計の挿入
まず、ステップを追加します
次に追加したステップの数式にてレコードを追加します
尚、追加したレコードは後で呼び出して本来のテーブルの最後に追加します
レコードは前述のような形で次のように指定します
ステップ名はmyTotalとしておきます
ここからステップを再び追加します
次に最終処理として数式にTable.InsertRows関数を指定し、mySubTotalにmyTotalレコードを追加する形にします
Table.InsertRows関数の文法は次の通りとなります
=Table.InsertRows(①テーブル,②挿入行の位置,③追加するリスト)
今回の①はmySubTotalを指定します
②の挿入行の位置の指定は、TableRowCount関数を使用します
③はmyTotalを使用しますが、リスト化するために波括弧：{}でくるみます
= Table.InsertRows(mySubTotal,Table.RowCount(mySubTotal),{myTotal})
これで完成です
＜まとめ＞
今回はM関数を組み合わせて小計と合計を追加する方法を解説しました
Power Queryは列単位では一律に処理するので意外と感じられる方も多かったのではないでしょうか？
今回の内容はレコードとは何か？テーブルとは何か？そしてM関数とは何か？について学べる内容になっています
ぜひ手を動かして試してみてください
【パワークエリ・チャレンジ】列数が可変の表を組み合わせて計算する
【M関数を利用することで、数式の修正しなくても列数が可変の表を組み合わせて計算できるようにします】
値を別表で作成した上で計算を行うケースはよくあると思います
上の画像のケースでは、ボーナスを計算するのに基礎となる評点を作成した上で各ポイントをクリアしていれば「ポイント別」に加算点を計算しています
例えば、上の画像で言えば田中さんはポイント1と2をクリアしているので、80*9%と80*10%が加算点の合計点になります
上記では関数で計算していますが、問題はポイント数が変わった場合です
ポイント数が変わるたびに関数を変更しなくてはなりません
今回の記事では、いくつかのM関数を組み合わせたカスタム列を作成することで、計算の対象列数が可変でも計算が正しくできるようにします
使用するデータ
２つのテーブルを使用します
上の画像は「tableMatrix」です
こちらのテーブルを読み込んだクエリでカスタム列を作成します
上の画像は「tablePoint」です
こちらもカスタム列内で使用します
最終的に作成するカスタム列
今回の記事で最終的に作成するカスタム列は次の通りとなります
一見複雑ですが、List.Accumulate関数の箇所を紐解けば一気に理解のハードルが下がります
List.Accumulate関数
次のリスト：myAccumulateを基にしてList.Accumulate関数でリストの合計値を計算してみます
変数：state,currentが出てくるので一見ややこしいです
= List.Accumulate(myAccumulate,0,(state,current)=>(state+current))
文法は次の通りとなります
=List.Accumulate(リスト名,計算の最初の値,計算式）
話を分かりやすくするために、実際にList.Accumulate関数の計算履歴を追ってみたいと思います
計算式1 ⇒state:0(計算の最初の値)+current:1 ⇒1
計算式2 ⇒state:1(計算式1)+curernt:2 ⇒3
計算式2 ⇒state:3(計算式2)+current:3 ⇒6
つまり、リストの中身を変数を使用した計算式にて順次計算して累計処理をしているわけです
では、最終式のList.Accumulate関数の中身を実際に分解していきたいと思います
Table.ColumnNames関数
List.Accumulate関数の第一引数はステップ名：myPointです
myPointはTable.ColumNames関数で算出された値です
Table.ColumnNames関数はテーブルの列名をリスト化します
今回はポイントを設定したtablePointクエリの列名をリスト化します
関数の引数はリスト名を指定するだけでOKです
このリストがList.Accumulate関数の第一引数になるということは、計算式（state+current）内の変数：currentには「ポイント1・・・」などのテキストが繰り返し代入されるとこになります
どうやって計算が行われるのか？と不思議になる読者の方もいらっしゃると思います
この点は次のRecord.Field関数の箇所で解説します
Record.Field関数
Record.Field関数は特定のレコード中から特定のフィールドの値を抽出します
ちなみに、上の画像のtablePoint{0}*はtablePointテーブル/クエリの1行目のレコードになります
今度は第二引数を”ポイント2″を指定してみます
ポイント2のフィールドの値が抽出されます
List.Accumulate関数内では「ポイント1,2・」が繰り返し変数：currentに入ります
この変数：currentはRecord.Field関数の第二引数に設定され、数値が抽出されます
計算式の追跡
では、ここでList.Accumulate関数の計算式を追跡してみましょう
(state,current)=>①state+②Record.Field(tablePoint{0},current)*③Record.Field(_,current)*④[評点]
追跡するのは次の行/レコードです
計算式１:state/0(計算の最初の値)、current/”ポイント1″
①：0(計算の最初の値・第二引数)
②：Record.Field(tablePoint{0},”ポイント1″)=0.09
③：Record.Field(_,”ポイント”)=1
④：[評点]=80
①+②*③*④の計算式により7.2が算出されます
ここで注意点です
③の_はカスタム列を作成しているクエリのレコードをそのまま受け継ぎます
今回の場合は次の画像のレコードを受け継ぎます
計算式２:state/7.2(計算式1の値)、current/”ポイント2″
①：7.2
②：Record.Field(tablePoint{0},”ポイント2″)=0.1
③：Record.Field(_,”ポイント”)=1
④：[評点]=80
①+②*③*④の計算式により7.2+8=15.2が算出されます
計算式３:state/15.2(計算式2の値)、current/”ポイント3″
①：15.2
②：Record.Field(tablePoint{0},”ポイント3″)=0.12
③：Record.Field(_,”ポイント”)=0
④：[評点]=80
①+②*③*④の計算式により15.2+0=15.2が算出されます
一見、数式は複雑そうですが、List.Accumulate関数の変数の中身を紐解くとそれほど難しくないことが分かります
＜まとめ＞
今回は列数が可変の複数の表を組み合わせて計算する方法を解説しました
M関数を組み合わせることで、列数を可変にした計算が可能になります
最後に注意点です
let式を使用していますが、「,」を付ける位置を注意する必要があります
inの前、letの最後は「,」を付ける必要がありません
逆にそれ以外のステップは「,」が必要となります
参考までに使用したファイルを添付します
置き換えのIF式を簡略化する方法～リスト活用、列削除省略～
IF式は便利ですが、作成後のメンテナンスが大変だったりします
１つ内容を追加するだけでも、or以降のコードを追加しなくてはなりません
今回は、リストを使ってIF式の作成を簡略化する方法と、IF式自体を列に置き換える方法を紹介します
List.Contains関数
今回はList.Contains関数をIF関数内で活用します
このM関数はリスト内に該当するものがあれば「TRUE」そうでなければ「FALSE」を返します
上のGIF画像でいえば、リスト{1,2,3}に対して３は含まれているのでTRUE、４は含まれていないのでFALSEを返しています
置き換えのIF式にリストを埋め込む
下の画像の商品コードを①か②にグループ分けをしたいとします
以下のようなイメージです
その時は、通常は以下のようなIF式を使ったカスタム列を挿入すると思います
今回は、or式の部分を前述のList.Contains関数で置き換えます
ただこのままだと、①に置き換える商品コードを追加するには、M関数のメンテナンスが必要です
ですので、次に
{“10001″,”10002”}の部分を別途作成するリストで置き換えます
このような感じです
これで、別途作成するリストの内容を置き換えれば、IF関数の出力も変ります
列削除の省略
置き換えのIF式を作成した後、既存の列を削除することもあるかと思います
工夫をすれば、列削除のステップを省略できます
まずは、
ダミーで変換タブから置き換えを行います
置き換えの内容はなんでもいいです
すると、数式タブの内容が次のようになっているはずです
こちらを次のように置き換えます
A:置き換え前の文字列の箇所は「each [商品コード]」とします
A’:置き換え後の文字列の箇所に先ほどのコード「if List.Contains(myCode,[商品コード]) then “①” else “②”」を使います
ここでポイントは「each」をうまく使うことです
eachを使うことで「各々の行」という意味になります
＜まとめ＞
今回は、置き換えのIF式をうまく簡略化する方法を解説しました
Power Queryはとにかく便利ですので、ついついクエリのステップが長くなったり、後でメンテナンスが面倒になったりするケースもあります
今回のように、工夫すれば簡略化できますので、ぜひ活用してみてください
M関数を使用して、可変で出現する特定文字の下の行を切り捨てる
以前、出現する特定文字の上の行を可変で削除する方法は過去の記事で紹介しました
今回は「特定文字の下」を削除する方法を解説したいと思います
上の画像に「部門」という文字が出現しますが、こちらの文字以降の行を可変で削除します
「部門」の文字が5行目に現れようが、100行目が現れようが、それ以降の行を削除できるようにします
ポイント
今回のポイントはM関数「List.PositionOf」を使用し、特定文字の行位置を取得することです
そして、行位置を取得するＭ関数は数式バー内に仕込むのもポイントです
List.PositionOf
改めて、M関数/List.PositionOfについて解説します
このM関数はリストの中から、特定の文字の位置を抽出します
文法は以下のようになります
List.PositionOf(リスト,文字列)
今回はこの「特定の文字の位置」を行位置に置き換えて使用します
List.PositionOf関数の第一引数のリストには、今回は「前ステップ名+列名」を使用します
行の保持
ホームタブに行の保持というメニューがあります
今回は「上位の行を保持」を使用して、特定の文字以降の行を削除します
メニュー通りに使用すると、下の画面で保持する行数を指定します
ここで注意して頂きたいのは、上の画像の行数を指定する箇所にList.PositionOfを埋め込むわけではありません
こちらには一旦、仮の行数を入力します（今回は5）
すると、上の画像のように「5」という数字が数式バーに入ります
こちらをM関数に置き換えます
数式バーの内容変更
上の内容では仮の行数で「行削除」が行われました
こちらをM関数により可変にします
その前に「行削除」が行われる前ステップの状態を確認します
受注NOの列に「部門」の文字列があります
こちらのステップ名と列名をM関数内のリストとして使用します
ですので、以下の画像のように数式バーを変更します
= Table.FirstN(昇格されたヘッダー数,List.PositionOf(昇格されたヘッダー数[受注NO],”部門”))
M関数：Table.FisrtNの第二引数に「５」と入力されていたところを、List.PositionOfに置き換えます
これで、特定文字で行削除を行う仕組みの完成です！
＜まとめ＞
今回は、List.PositionOf関数を使用して、特定文字列から下の行を可変で削除できるようにしました
内容を最初聞くと、難しそうに感じられる方もいらっしゃったかと思いますが、ダミーで行削除したステップの数式バーに関数を埋め込むだけなのでとても簡単です
記録マクロを修正するような内容でも、とても本格的なことができるのがPower Queryの良さですね！
Power Queryをクラウドで活用する方法～Power Automate使用～
【Power QueryをOneDriveなどのクラウドのエクセルでうまく活用する方法を解説します！】
クラウドがビジネスの主戦場になってきた今、Power Queryをクラウドでうまく活用できないか？という声を最近聞くようになりました
「Power Apps/Dataverse」「Power Automate」をうまく使用すれば、Power Queryをクラウドでも有効活用できます
ポイント
Dataverse
Dataverseでテーブルを作成する際、データソースにエクセルを指定するとPower Queryを使用した、データフローが作成されます
つまり、クラウド上のエクセルを触るわけではないですが、Power AppsではエクセルをデータソースにしてPower Queryを使用できます
Power Automate
Power Automateはクラウドで使用するRPAです
Power Automateでデータフロー/Power Queryを使用して作成したテーブルを、OneDrive上に転記することができます
ですので、Power Apps/DataVerseｘPower Auttomeの組み合わせをすれば、クラウド上でPower Queryを活用できます
データフローの作成
まずはテーブル画面からインポートを指定します
次にデータソースを「Excelブック」で指定します
データソースを指定したら、OneDrive内のファイルを指定します
次に取得元のテーブルを指定します
ここからはPower Queryエディターが立ち上がります
エクセルでPower Queryエディターを操作する場合は、クエリを作成後、読み込み処理を行います
Dataverseの場合には、読み込みではなく、既存のテーブルへのマッピング（列の割り当て）を行います
ただし、作成済みの既存のテーブルにマッピングするのではなく、全く新しくテーブルを作成する場合には「新しいテーブルに読み込む」を指定すればOKです
ちなみに「読み込まない」を指定して、データフロー上だけでテーブルを使用することもできます
Power Automate
こちらでは、要点を抜粋して記事を書かせて頂きます
データフローを更新するには、「データフローを更新する」アクションを使用します
データフローを更新した後に、OneDrive上にデータフローで更新したテーブルの内容をコピーするには、まずは「行を一覧にする」アクションを使用します
一覧にしたテーブルの各行は、繰り返し、OneDrive上のエクセルに追加します
＜まとめ＞
今回は、クラウド上でPower Queryを活用する方法を解説しました
Power AppsではPower Queryエディターを使用できるのでとても便利です
今回の記事ではPower Automateに関しては、要点を抜粋して記事を書かせて頂きました
実際には、使用したい内容に合わせて調整が必要です
例えば、データフローを更新すると、自動的にデータがテーブルに追加されます
ですので、時には、テーブルを削除するという対処方法が必要な時もあります
後、データフローを更新した際に、待ち時間を設定する必要も時にはあるかと思いますので、この点を意識しておく必要があります
受付終了しました⇒【Udemy講座リリース】とても簡単！明日から即使えるPower Query・表変換レシピ集
Udemyの講座の第二弾をリリースしました！
しばらく無料で購入できますので、購入してみてぜひ意見等をお寄せください！
＊無料期間は前触れもなく終了する時がありますので、あらかじめご了承ください
⇒受付終了しました
内容としては、セルの結合に加えて行列の見出しが複数ある表などの「規則性の低い」表を、Power Queryで簡単に規則性の高い表に変換する方法を解説するものです
表変換に困っている、エクセルの手動処理から解放されたいという方にぜひ見て頂ければと思います
表変換のレシピは５つ用意しています
どれも実用性の高いものばかりですので「明日から」活用して頂ける内容だと自負しております
尚、Udemy講座第一弾については、ただ今セールスをしております
Power Queryを極めたい人必見！！M関数講座 ⇒ 特別クーポン
ガッツ鶴岡
