タグ別アーカイブ: Power Query
上級編1回目サンプルPDFファイル
中級編9回目サンプル
置き換えの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関数講座 ⇒ 特別クーポン
ガッツ鶴岡
UDEMYの動画コース「Power Queryを極めたい人必見！！M関数講座」の無料・勉強会の開催報告
昨日、リリース済みのUdemy動画コース「Power Queryを極めたい人必見！！M関数講座」の無料勉強会を開催しました
参加して頂いた皆様には感謝の一言です！
今後の動画コースの内容、並びに、Power Queryの技術改善に多くの示唆を得ることができました！
今回の勉強会では2点「let式を使用した複数ステップの集約」「クエリの関数化」に絞ってお話させて頂きました
長いクエリの対策
Power Queryは便利なので、Power Queryに慣れてくるとどうしても適用したステップが長くなります
ステップが長くなると、後で見返してもどこがどうなっているのかわからなくなったりします
この点が、皆でPower Queryを共有して使う時にも問題となります
今回、日常的に業務でPower Queryを使用している参加者の皆さんと議論させて頂き「長いクエリ」への対策が必要なことがよく認識できました
そして「let式を使用した複数ステップの集約」の技術が長いクエリへの対策に役立つことも、参加者の皆さんに意見や感想をお聞きしてよく認識できました
下の画像のように、カスタム列作成画面でlet式を使用すれば複数ステップを１つのステップに集約することができます
しかも、コメントなども入れれば処理が複数ステップに分かれている場合よりもステップの意図がわかりやすくなります
let式の文法に慣れる必要がありますが、この方式を積極的に使えば、長くクエリをシンプルにわかりやすくできます
クエリの関数化
「販売単価の列」「生存原価の列」と「販売個数の列」を乗算し「利益を計算する列」を作成するなどのカステム関数はこのブログでも紹介しました
今回解説したのは、クエリ自体を関数化する方法です
通常のカスタム関数では各行にて１つの値（販売単価、製造原価、販売個数⇒利益）が出力されますが、クエリの関数化の場合には値の代わりにテーブルが算出されます
今回紹介したのは、ファイル内のテーブルを取得するクエリの関数化ですが、思った以上に使い道がありそうです
複数ファイルを取得するのは、フォルダ内の複数ファイルを取得する方法でも行えます
参加者からおききしたのですが、この方法だとクエリが重くなり、フォルダからファイルを幾つか抜き出すケースもあるそうです
確かにフォルダ内のファイル一括取得は便利ですが、日常業務では確かに頻発しそうな問題だと感じました
今回紹介したデータ取得するクエリの関数化ではフォルダ内のファイルを細分化して取得することができるので、クエリが重くなる可能性を低くできます
データ取得以外にもクエリの関数化の用途がありそうなので、今後も積極的に研究していきたいと思います
＜まとめ＞
今回は本当に貴重な意見をいくつもお聞きすることができました
今後もユーザーの方との交流しながら、自分自身のスキルアップを図っていきたいと思います
Udemyの動画も頂いた指摘を基にして修正を行っていきたいと思います
⇒Udemyの動画・詳細はこちらから
Udemyの動画コースの内容についての無料・勉強会～M言語に触れる60分～
先日、Udemyで動画コース「Power Queryを極めたい人必見！！M関数講座*」の発売を開始しました
*期間限定の特別価格・1600円のクーポンです
なかなか他では知ることのない技術が詰まっています！
今回は、この動画コースの一部を体験して頂きたいと思い、無料勉強会を開きたいと思います
ぜひ、これから「M言語の素晴らしさ」や「Power Queryを通常のエクセルワークシートのように自由に扱う面白さ」を一人でも多くの方に広めていきたいと存じます！
概要
・開催者/解説者：ガッツ鶴岡
IT講師をしております。Power QueryやPower BI、RPAなどを得意としております。ストアカ・ゴールドバッチ
・日時：2022年7月18日（月曜日・祝日）
①13：30-14：30⇒受付停止、②16:00-17:00⇒受付停止、③20:00-21:00⇒受付停止
・料金：無料
・内容：クエリの関数化、複数ステップの集約等
-クエリの関数化：作成済みのクエリ自体を関数化します。クエリを関数化すると、別なクエリの各行に対してテーブルを作成することができます。今回は複数ファイルの内容を、別途指定した内容のクエリを基にして一括取得します
-複数ステップの集約：let式を使用して、複数ステップの内容を1ステップの集約します。この技術を活用すると長いクエリをシンプルにすることができます
詳細・お申込み
こちらからお願いします
以上、ぜひ一人でも多くの方にご参加いただきたいと存じます！
（続編）２つの表にてどの行が追加、削除されている？ ⇒どの列が変更になっている？
前回の続きで、パワークエリを使用して行だけでなく列についても、値の変更もあわせて変更箇所を特定できるようにしたいと思います
ポイント
２つの表を結合するのは前回と一緒です
今回はピボット解除を行い、複合キーを作ったうえで結合を行います
使うデータ
Before、Afterの２つの表を用意するのですが、キー（りんご、みかん・・）を行単位で作成し、列は月単位で作成します
最後は２つの表でどこが変わったかを明確にできるようにクエリを作成します
ピボット解除＆複合キー作成
BefoerとAfter、それぞれのクエリはピボット解除を行い、縦横並びを縦縦並びに変えます
その後、「列のマージ」を使用して、複合キーを作成します
この複合キーを使用して、BeforeとAfterの２つの表を結合（マージ）します
マージ処理
ポイントでも前述したように、２つの表を複合キーを使用してマージ処理します
マージ種類は通常でいけば、完全結合がよいです
「完全外部」の結合であればAfterの表にて追加・削除されたものが全て網羅できます
但し、こちらは用途にあわせて６つの結合種類を使いわければよいかと思います
各種調整
判定列の追加
マージ後は、条件列を使用して〇×を判定する列を追加することができます
複合キーの分解
マージ後は複合キーを分解して必要な情報だけ抜きだすこともできます
複合キーの分解は「抽出」の「区切り記号の後のテキスト」を使用し、複合キーから「月」を抜き出します
そうすると、BeforeとAfterの比較が行いやすくなります
＜まとめ＞
今回はマージを更に有効活用して、行だけでなく、変更があった列と値を特定できるようにしました
ポイントはピボット解除と複合キーの作成により、２つの表を比較しやすい形で結合することです
ぜひ有効活用してみてください