タグ別アーカイブ: M言語

Power Queryに強くなる~M言語/コードに慣れましょう~ 第10回

【Power Queryはあくまでコードでできています。これが今回のポイントです】

Power Queryはとても便利です。メニューをクリック操作していくだけでかなりのことができます

但し、メニュー操作に慣れてしまうと、エラーが起こった時に対処方法が分からなかったりします

そして、もう一つ問題があります

Power Queryが登場してから数年が経ちました

他の方が作成したPower Queryをメンテナンスする機会も増えていくと思います

この時、コード自体を理解できないとメンテナンスは行いにくいです

ですので、これからはPower Queryで書かれているMコードを理解できることも重要になっていくと思います

私も他の方が作成したPower Queryを修正する仕事をしていますが、Mコードを直接理解できないと仕事になりません

今回の記事を通じて、Mコードに慣れる機会にして頂けると嬉しいです

但し、今回の記事ではMコードを書くことは意図していません

あくまで、Mコードを読めるように訓練していく必要性と、Mコードを読む勘所を掴んで頂きたいと思います

今回のポイント

Power QueryのMコードはStepが連なってできています

ここがM言語の最大のポイントです

しかも前後左右、Step間で整合性が取られています

このStepに慣れるとそのままM言語に慣れることができます

Stepの中身の確認

ここからは実際に同じように手を動かしてみて頂くと分かりやすいと思います

たとえば、以下の画像のような平均金額を計算したPower Queryがあるとします

もちろんStepは「ソース」からはじまっています

Stepの中身は2つの方法で見ることができます

一つは数式バーです

二つめは詳細エディターです

こちらはホームタブから見ることができます

詳細エディターをクリックするとコードを丸ごと見ることができます

コードの中身を見て頂くと前後、Stepがつながっているのが良くわかります

let
①ソース = Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content],
②変更された型 = Table.TransformColumnTypes(①ソース,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),
③計算された平均 = List.Average(②変更された型[売上金額])
in
③計算された平均

Stepの意味合い

試しにこちらの数式の中身を「ソース」に変えてみます

画像に alt 属性が指定されていません。ファイル名: image-40.png

そうするとどうなるでしょう?

表示はソースStepの内容になります

つまり、Step名自体に意味があります

Step名そのものが「データテーブル」を意味します

よくあるエラー

ここからは実践的な内容として、よく起こるエラーと対策について紹介します

ソースの変更

ソースステップの数式を見てみましょう!

数式の中身は次のようになっています

= Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content]

試しにデータソースとなっている「売上データ」を「売上データ2」に変えてみましょう!

すると次の画像のようなエラーが発生します

この手のエラーはよく発生します

この場合、元のテーブル名に戻すか次のように数式を変更します

そうすればエラーは解消されます

変更された型

今度はソースステップで列名を変更してみます

そうすると以下の画像のようなエラーがでます

こちらはよく起こるタイプのエラーです

ところで「変更された型」Stepとはなんでしょう?

= Table.TransformColumnTypes(#”名前が変更された列 “,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}})

この「変更された型」Stepでは、各列の形式を設定しています

ここで1つ前にStepも含めて「変更された型」Stepを見てみましょう!

①名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
変更された型 = Table.TransformColumnTypes(名前が変更された列,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),

①名前が変更された列Stepでは「商品」列を「商品2」列に列名を変更しています

次の①変更された型では「Table.TransformColumnTypes(名前が変更された列」となっているので、①の名前が変更された列のテーブルデータを引き継いでます

つまり、「商品2」の列名を持つテーブルデータを引き継いでます

でも、②変更された型Stepでは商品列を「type text」、文字列形式にしています

{“商品”, type text}

ということは、既に存在しない列名を文字列形式に変更しようとしています

ですので、前述のエラーメッセージが出るわけです

画像に alt 属性が指定されていません。ファイル名: image-49.png

ではどうやってエラーを解消したらいいでしょう?

一番シンプルなのは変更された型Stepを削除することです

これで、Step間の整合性が取れるからです

let
ソース = Excel.CurrentWorkbook(){[Name=”売上データ2″]}[Content],
名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
計算された平均 = List.Average(名前が変更された列[売上金額])
in
計算された平均

では、どうやって形式変更を行ったらいいでしょうか?

手動で各列を変更するか、すべての列を選択した上で「データ型の検出」を行います

ちなみに、「変更された型」の自動設定を解除したかったら、ファイルタブから「クエリのオプション」を選択します

次に開く画面で「データの読み込み」から「非構造化ソース・・・を検出しない」を設定しておきます

そうすれば、次から「変更された型」Stepは自動で設定されなくなります

<まとめ>

今回の記事では、M言語/コードに慣れるきっかけになるような事例を幾つか紹介させて頂きました

この記事だけで飛躍的にPower Queryの技術が向上するわけではないですが、コードに注目していけば継続的に技術が向上していくはずです

今回の記事は本当に触りだけですが、本格的にM言語を学びたい方はこちらの記事をぜひご利用ください

尚、Udemyで動画コース「Power Queryのメニューにはない裏技ができる! M関数を学んで使えるようになる講座」をリリースしています

期間限定ですが、無料クーポンを提供させて頂きますので、ぜひご利用ください!

終了したら、レビュー投稿の程よろしくお願いします!

今後の参考にさせて頂きます!!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

クエリの関数化事例~複数シートを一括で編集する

【一度作成したクエリを関数化して複数シートに使いまわし、結果的に一括処理する】

エクセルファイル内で複数シートに分かれていると、一括処理ができなくて困ることがあります

一番、困るのは各シートにヘッダーデータがあるようなケースです

このような場合はまずは1シートを処理するクエリを作成します

そして、そのクエリを関数化します

関数化するということは、Y=2X+1のような式で表せるということです

こちらのXには複数シート名を入れて使いまわせるようにします

SUM関数/SUM()では()の中身を変えれば、それぞれの合計が出力されます

今回は、()の中身が次から次へと格納されて、連続的に出力されるイメージになります

使用データ

前述のように各シートにはヘッダーデータがあります

その下に欲しい一括処理したいデータがあります

そして、上記のようなシートがファイル内に複数あります

各シートの開始行などは一致している前提です

上の画像は東京のシートですが、次の画像は大阪のシートです

関数化するクエリの作成

まずは1シートを処理するクエリを作成します

こちらのクエリを前述のように関数化します

今回はテーブル化からはじめず、ファイルからデータを取得します

指定するファイルは「今処理しているファイル」です

ファイルを指定したら、ナビゲーター画面が開きます

こちらでは1つのシートを指定し「データの変換」をクリックします

ここでクエリは複製しておきます(1つは関数を作成した後に、関数を適用するクエリになります

下の画像がPower Queryエディタを開いた状態です

必要なのは11行目から下です

こちらは「行の削除」アクションで10行削除します

ここからもう1アクションあります

変換タブで「1行目をヘッダーとして使用」アクションを使用して、見出しを整えます

これで関数化のもとになるクエリは完成です

関数化

関数化は詳細エディタで行います

具体的に関数化する箇所は「可変の箇所」、シート名の箇所です

ここでソースステップの内容を確認しておきましょう

要はファイル内の複数シートを取得しています

ここからナビゲーション画面で1つのシートを選択しています

ですので、作成した関数を適用する場合は「東京、大阪、名古屋」と関数化した箇所に連続して格納されるようにすればいいのです

では実際に記号Xを埋め込んでみましょう

(X)=>はXを記号として使用するという合図になります⇒詳細

ここまで行うとクエリが関数に変ります

関数は「myFunction」と名前を変えておきましょう

ここでパラメーターに「名古屋」と入れてみましょう

これで名古屋のシートデータが変換されて出力されます

関数の適用

ここで関数の複製元のクエリを選択し直します

その後は、適用したステップでソースデータを残して後は全て削除します

ここから列の追加タブで作成済みの関数を呼び出します

関数のXに入るのは「Item」になります

これでXに東京、大阪、名古屋と値が格納され、クエリが作成されていくことになります

結果、次の画像のようになります

ここからmyFunctionを展開すれば完成です

次の画像が展開した後のものです

<まとめ>

今回は、ヘッダー行がある複数シートを、クエリの関数化を使用して一括処理しました

クエリの関数化のポイントは可変の箇所を見つけることです

今回は、1度作成したクエリから可変の箇所を見つけました

そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います

もう少し詳しく「クエリの関数化」について知りたいという方はぜひUdemy講座(動画)もご利用ください

期間限定で「4,800⇒1,200」の割引クーポンが利用できます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power Queryならではのカレンダーテーブル活用~経過営業日の算出

【M言語独自のリスト作成を通じて、経過営業日を自由自在に算出する】

エクセルのワークシートには経過営業日を計算してくれる便利な関数があります。Power Queryではその手の関数はないですが、独自のカレンダーテーブル活用により自由自在に計算方法をカスタマイズできます

上の画像のような連続した値を作成する方法は過去の記事でも紹介しています

今回の記事ではこういったPower Query・M言語独自のテクニックが詰まった内容になっています

ポイント

今回のポイントは関数などで一律に計算するのではなく、日付を並べたうえで計算を行います

上の画像のように該当する日付を並べて、曜日に変換し「0,1」を並べます

そして、最後に「0,1」の列を合計します

この合計が経過営業日です

ですので、後から条件式の追加などが容易です

上の画像では祝日が考慮されていませんが、祝日の考慮はもちろん、その他の条件の考慮も容易に行えます

カレンダーテーブルの作成

カレンダーテーブルの作成はリスト作成⇒テーブルに変換の流れで作成します

「..」を使用すると連続した値のリストが作成できます

この仕組みを利用して、他クエリからまずは連続した日付のリストを作成します

他のクエリとは下の画像のような2つの日付を記したクエリになります

期間_単数

こちらのクエリの各列の1行目を参照して、空のクエリでリストを作成します

*{0}で1行目を参照します

ここではNumber.From関数で一旦数値に変換しています

= {Number.From(期間単数[期間A]{0})..Number.From(期間単数[期間B]{0})}

連続した数値を作成した後は、テーブルに変換します

後は日付形式に変換を行います

曜日への変換

曜日への変換は「Date.DayOfWeekName関数」を使用します

曜日だけではなく、祝日も考慮が必要です

祝日については別途テーブルを作成しておく必要があります

こちらは作成した空のクエリのマージします

そして、曜日との集約を「条件列」で行います

これで「0,.1」を並べる列を作成する準備ができました

0,1列の作成及び合計

0,1を並べる列はカスタム列作成画面でif関数を使用して作成します

これで0,1が並ぶ列/Digitを作成することができました

尚、上記の式ですと「起算日の曜日」が休日でない場合には自動的に計算に含まれています

それでは本題に戻りますが、最後にこちらのDigit列を対象にして、変換タブから合計処理を行います

これで経過営業日を算出できました

ちなみに祝日クエリをマージした時に、日付の並びが変わってしまいますので、並び替えを行っておくと計算過程が分かりやすいです

計算方法の調整

起算日には曜日・祝日に関わらず計算に入れる(0を立てる)、もしくは計算に入れないなどの調整にはインデックス列を使用します

上記の画像のように、起算日の行になるのは「インデックス列」の1になります

ですので、if式をインデックスが1の時には値が0(もしくは1)になるように修正します

逆に計算の終点の日を曜日・祝日に関わらずに計算に入れる場合にはLis.Max関数を使用します

複数行計算する場合

複数行にて計算する場合には、カスタム関数を使用します

前述のクエリの詳細エディタを開き、コードに記号を埋め込んで関数化します

={Number.From(myDate[起算日A]{0})..Number.From(myDate[起算日B]{0})}

⇒{Number.From(A)..Number.From(B)},

上の画像のAとBの記号が、下の画像の各列と対応します

そして、記号を埋め込んで完了ボタンを押すと、関数は完成です

こちらの関数を複数行あるクエリで呼び出します

この際、前述の記号に各該当列を割り当てます

これで、各行に関数が割り当てられます

後は関数で作成した列を展開して終了です

<まとめ>

今回は経過営業日をPower Query独自のカレンダーテーブルにより算出する方法を解説しました

もしかしたら、上記の内容で「この画像の起算日の計算式は?」という内容もあるかもしれませんが、ご容赦頂ければと思います

今回の方法ですと、ワークシート関数で一律に計算するより、分かりやすく、カスタマイズしやすいです

ぜひ、手を動かしながら試してみて頂きたいと存じます!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

(無料期間終了)Udemy講座のリニューアル及び無料クーポンのプレゼントについて

昨年、Udemyにて「Power Queryのメニューにはない裏技ができる! M関数を学んで使えるようになる講座」をリリースしました。但し、多くの意見・指摘が寄せられたため今回、全面的に動画を撮り直しました

この機会に無料クーポンをこのブログ読者の皆さんにプレゼントしますので、ぜひレビュー投稿をお願い致します

頂いた意見・指摘

これまで多くの意見・厳しい御指摘が寄せられました

一方で、それだけこのテーマについては期待が高かったことを認識するとともに、期待を大きく裏切り大変申し訳ないことをしたと反省しております

これらの指摘・意見を参考にしてほぼすべての動画を撮り直しています

リニューアルポイント

動画の本数は以前と変わりませんが、動画は一から撮り直しました

動画によっては3倍の長さで撮り直しています

動画撮影にあたってはとにかく「間」を取りながら、動画のメリットをだせるように最新の注意を払いました

・作成するクエリの内容を丁寧に説明する

・前段でポイントとなる箇所を図で解説する

・操作動画は事前アナウンスをしながらゆっくりPC操作を行う

こちらがサンプル動画です

https://youtu.be/m53fBnCyJgA

レビュー投稿をお願いします!

講座の内容としては他ではなかなか学べない「Power QueryのM言語、M関数を実践的に使うためのテクニック」が詰まった内容となっています

しかも、コードを一から書くわけではないのでそれほど難しくはありません

この講座が終了した時には、Power Queryの凄さや面白さを体感して頂ける内容だと自負しています

こちらが講座の無料クーポンとなります⇒お申込み

ぜひ、講座をご覧いただきレビュー投稿をお願いします

すぐにではないですが、頂いた意見は次に活かしていきます

尚、無料期間は突然終了する可能性がある点は予めご了承ください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

パワークエリ記事一覧

M関数逆引き辞典はこちらから!

初級編

タイトル 内容
Power Queryで操作はどう変わる?~初級講座1回目 パワークエリとはなにか?そのメリットをスマホに例えて解説します
シートからデータ取得~ 初級講座2回目~ 別シートのデータをテーブル化して取得する方法を解説します
ファイルからデータ取得 初級講座3回目 別ファイルのデータをテーブル化して取得する方法を解説します
フォルダから複数ファイルデータを一括取得~初級講座4回目 フォルダ内にある複数のファイルからデータをまとめて取得する方法を解説します
ブログ管理人・Udemy講座

中級編

タイトル 内容
複数データの組み合わせ/クエリのマージ~中級編1回目~ クエリのマージ技術の概要について解説します
クエリのマージ/項目追加~中級編2回目~ 複数のクエリをマージする方法について、実際のデータで解説します
クエリのマージ/計算処理~中級編3回目~ マージした項目を活用して計算処理を行う方法を解説します
クエリの追加作成~中級編4回目~ 同じ項目のクエリを追加して結合する方法を解説します
クエリの追加・結合~中級編5回目~ クエリの追加をピボットテーブルと連携して行う方法を解説します
ピボット解除/縦横並びの変換処理~中級編6回目 縦横の使いにくいデータを、使いやすい並びに変える方法を解説します
セル分割他・変換処理~中級編7回目 複数のデータが1つのセルに入っている場合に分割する方法を解説します
セル分割他・変換処理2~中級編8回目 前回の内容を使いやすい形式にして出力する方法を解説します
エディター内のやり直し操作~中級編9回目~ ワークシートにおけるCtrl+Zに相当する処理の方法を解説します
変換した列の追加~中級編10回目~ 連番、条件列(IF関数に相当)及び列削除・移動他を解説します
グループ化1~中級編11回目~ データを階層化して集計する方法を解説します
複数条件でグループ化~中級編12回目~ データを複数の条件で階層化する方法を解説します

上級編

タイトル 内容
行削除とヘッダー行の調整~上級編1回目~ 見出し行/ヘッダーがおかしい場合の対処方法を解説します
セル結合により見出しが2行の表をデータ活用1~上級編2回目~ セル結合された使いにくいデータを使いやすくする方法を解説します
セル結合により見出しが2行の表をデータ活用2~上級編3回目~ セル結合された使いにくいデータを使いやすくする方法を解説します
各種集計/合計から四捨五入まで~上級編4回 四則演算の計算から四捨五入まで、様様な集計方法を解説します
エラー修正_処理ステップエラー~上級編5回目 ファイル保存先変更などに伴うエラーの対処方法について解説します
途中のステップを削除した場合のエラー修正処理~上級編6回目 途中のステップを削除した場合のエラー対処処理について解説します
エラー修正_データ自体のエラー~上級編7回目 データ自体がエラーになっている場合の対処方法について解説します
カスタム関数を自身で登録して使い回す~上級編8回目 ユーザーが独自で作成するカスタム関数の作成方法を解説します
カスタム関数を自身で登録して使い回す2~上級編9回目 作成したカスタム関数を呼び出して活用する方法を解説します
IF式を組み合わせて列作成~上級編10回目 条件付きカスタム列を作成する方法を解説します
「例からの列」にて変換パターン自作~上級11回目~ 入力した変換例を汲み取って変換を行う方法を解説します
「 例からの列」による桁数が規則列への対応~上級12回目~ 「例からの列」による変換方法の応用方法を解説します
エディタを開かずにソース変更処理~上級13回 「パラメーター」機能を使用してデータソースを変更する方法を解説します
シート上からデータソースを変更する方法~上級編14回目 シート上の入力を変更するだけでデータソースを変更する処理を解説します
セルの値を変更するだけで読み込みを変更する方法~上級編15回 セルの変更内容をクエリに組み入れて、クエリの内容を変更できるようにする方法を解説します
クエリのコピー・バックアップ・削除他~上級編16回 クエリのコピーやバックアップ及び削除、その他グループ化などの方法を解説します
文字列の抽出~上級編17回 RIGHT関数やLEFT関数、MID関数に相当する内容に加え、特定の文字列を抽出する裏技を解説します
文字列の追加、置換~上級編18回 前回の特定の文字列の抽出に加え、文字列の追加や置換方法について解説します
エラー発生の予防/列のデータ形式変更と削除について~上級19回~ エディタ内の操作のちょっとした工夫で、エラー発生を予防する方法を解説します

M言語編

タイトル 内容 使用するM関数
M言語に慣れる_1回目~コード構造の把握~ Mコードの基礎的なルール/文字の色、大文字の区別、ステップの参照について解説します List.Average/平均値・計算
M言語に慣れる_2回目~カスタム列~ カスタム列・作成画面でM関数を使用して新たな列を作成する方法を解説します Text.From/文字列への変換
M言語に慣れる_3回目~ダイナミックフィルタリング~ セルの変更内容をフィルタイングの内容に反映する方法を解説します List.Max/最大値の計算
M言語に慣れる_4回目~特殊テンプレート作成~ 2つのクエリを組み合わせて、1担当者に対して複数の勤務日がある表を作成する方法を解説します
M言語に慣れる_5回目~M関数を検索する方法~ ワークシート内の関数のように、M関数を参照する方法を解説します Text.PositionOf/特定の文字列の位置抽出
M言語に慣れる_6回目~3種類の括弧~ 丸括弧と角括弧そして波括弧とテーブル、リスト、レコードの関係を解説します
M言語に慣れる_7回目~空のクエリからテーブル作成~ 空のクエリから、リストやレコード、テーブルを作成する方法解説します #table/テーブル作成、Table.RenameColumns/列名変更
M言語に慣れる_8回目~eachを使いこなしてM関数作成~ 数式バーに良く出てくる「each」の用法について解説します Table.SelectRows/テーブルから特定の行を抽出、List.Select/リストから特定の値を抽出
M言語に慣れる_9回目~複数シートをコード1行で結合~ ファイル内のシートからデータをまとめるクエリを作成する方法を解説します Excel.CurrentWorkbook/ワークブックの内容を一括抽出
M言語に慣れる_10回目~グループ毎に連番作成~ 「グループ化の操作/全ての行」を活用してグループ毎に連番を作成する方法を解説します Table.AddIndexColumn/連番作成
M言語に慣れる 11回目~前行を参照して計算 連番と波括弧:{}を組み合わせて活用して前行を参照する方法を解説します
M言語に慣れる~12回目例外処理 エラーが発生した際の対処処理を行う方法を解説しています
M言語に慣れる13回~ダイナミックに連続した日付作成 ワークシート内にある連続性のない日付から、連続する日付を作成した後、他データも紐づける方法を解説します #date/「年、月、日」から日付を作成、#duration/「日、時間、分、秒」から期間値を作成します、List.Dates/「開始日、リスト個数、増分」から日付のリストを作成、Number.From/値を数値型式にして返します
M言語に慣れる14回目~複数行を1つのセルにまとめる グループ毎のテーブルを作成後、グループ別に1つのセルにテーブルの内容をまとめる方法を解説します
M言語に慣れる15回目~TEXT関数/文字列関数 テキスト関数の事例について解説します Text.Contains/文字列の有無を判定、Text.Replace/文字列の置換
M言語に慣れる16回目~List関数 リスト関数の事例について解説します List.Max/最大値の計算
不規則に散らばっているデータを一括取得~M関数~ 見出し行/ヘッダーがそれぞれ違うシートの内容を一括でまとめる方法を解説します List.PositionOf/リストの中から特定文字列のリスト内位置を抽出、Table.RemoveFirstN/テーブルの先頭から指定行数を削除します、Table.PromoteHeaders/先頭行を新しい列見出しにします
参照先バッファー化によるパフォーマンス向上~M関数~ クエリのパフォーマンスを向上させる方法を解説します Table.Buffer/参照テーブルのバッファー化
クエリの列名をダイナミックに変更する~M関数使用~ 入れ子のリストを使用して、ワークシート上の内容でクエリの列名を変更する方法を解説します Table.RenameColumns/列名変更、Table.ToColumns/テーブルからリスト(入れ子)作成
Power Queryエディタ上で複数条件付き合計処理~M関数使用 M関数を組み合わせて「SUMIFS関数」と同様の計算をエディタ上で行う方法を解説します Table.SelectRows/条件に合う行のみを抽出、List.Sum/合計値を抽出
指定した範囲の値を使用してフィルタリング M関数を利用して、ワークシート上の表にある値に一致するもののみをフィルタリングする方法を解説します List.Contains/リストに指定の値が入っているかを判定
複数リストを1対1の関係でうまく組み合わせる 1行に複数の値を持つリストを複数組合わせる場合に、リスト間で1対1の関係になるようにします(例:8月2日/月、火、水➡8月2日/月) Table.FromColumns/複数のリストからテーブルを作成
列名をダイナミックにしたピボット解除を行う 新たな列を追加した場合でも、並べ替える列とそうでない列をコントロールできるようにする Table.ColumnNames/列名リストを作成
PDF変換の応用~フォルダに入れた複数PDFから都合良くデータを切り取る方法 複数のPDFファイルから都合よい箇所だけデータを一括抽出できるようにする Table.RemoveFirstN/指定行から上の行を削除
List.PositionOf/リストの中から特定文字列のリスト内位置を抽出
【POWER QUERY裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法 フォルダ内のファイルを一括で取得する際に、パラメーターを使用して取得するシートを可変にします Excel.Workbook/Excel ブックの内容を返します。
M関数を使用して、可変で出現する特定文字の下の行を切り捨てる 特定文字列の下の行を可変で削除します List.PositionOf/リストの中から特定文字列のリスト内位置を抽出
置き換えのIF式を簡略化する方法~リスト活用、列削除省略~ メンテナンスが面倒なIF式を簡略化する方法を解説します List.Contains/リストに特定の値が含まれていればTRUEを、そうでなければFALSEを返します
POWER QUERYならではのカレンダーテーブル活用~経過営業日の算出 M言語独自のリストの活用により、経過営業日の算出を自由自在に行えるようにします Day.DayOfWeekName/曜日変換
クエリの関数化事例~複数シートを一括で編集する ヘッダーデータがある複数シートを一括処理します

分析編

タイトル 内容
POWER QUERYで手軽にABC分析 パワークエリのM関数をうまく組み合わせれば、ABC分析も行うことができます
RFM分析の「R」を集計する 注文データから注文者(会員)
別に最終注文データを集計できるようにします
RFM分析の「F」を集計する 注文データから注文者(会員)
別に注文頻度を集計できるようにします
RFM分析の「M」を集計してまとめる 注文データから注文者(会員)別に注文金額を集計できるようにします

コラム

タイトル 内容
【パワークエリ・チャレンジ】グループ別の小計と合計を表示する 今回は本来は一律処理のPower Queryに行挿入を行います
【パワークエリ・チャレンジ】列数が可変の表を組み合わせて計算する List.Accumulate関数を他のM関数と組み合わせて使用します
POWER QUERYをクラウドで活用する方法~POWER AUTOMATE使用~ DataverseとPower Automateを組み合わせて、Power Queryをクラウドで活用します
(続編)2つの表にてどの行が追加、削除されている? ⇒どの列が変更になっている? ピボット解除と複合キーを活用して2つの表を比較しやすくします
どの行が追加、削除されている?~結合の6種類~ 結合の各種類をつかいこなせば、行の追加や行の削除の状況が一目でわかります
【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換 行列双方向にセル結合がある表をテーブル形式に直します
【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法 実は行列の入れ替えを行えば、列方向でのフィルター処理が可能です
【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する 余りの計算とピボット機能を組み合わせて1列のデータから複数列のテーブルデータを作成します
【M言語実践】グループ別に累計を集計する方法 グループ別に累計値を集計する方法を解説します
【M言語実践】顧客の2回目のリピート率を分析する方法 通常のエクセル処理であれば相当煩雑な分析処理をM言語を活用して簡略化します
M言語実践】グループ別にIDを設定 グループ別に連番を採番した上で桁数を揃えてIDを作成します
マスタデータの履歴管理を行う方法 マスタデータが重複している場合に、日付が最新のもののみを表示する方法を解説します
POWER QUERYのステップ数を減らす3つの工夫 「変更された型」のステップを減らすなどの工夫を解説します
エディタからデータ入力してクエリ作成 エディタ上で直接データを入力する方法を解説します
【豆知識】どこの行が変わったかを調べる方法 クエリのマージの仕方を工夫しつつ、条件列を組み合わせて、元データと修正があった箇所を特定する方法を解説します
【豆知識】ピボット/縦縦並び⇒縦横に並び替え集計 ピボット解除と別な方向で並べ替える方法を解説します
【豆知識】ピポット解除時の空欄の扱い グループ化
【分析作業用】大量データを1目で把握する パワークエリの表示タブでは大量のデータを1目で分かるようにする為の工夫がありますので、そちらを解説します
【分析作業用】グループ毎の平均と個の平均の差を自動集計する方法 グループ化機能を2段階で行うことで、グループ単位の集計と個々の集計とを同時に行う方法を解説します
クエリの複製、参照、そしてコピーとの違い~ クエリを右クリックした時に出てくる「複製」「参照」「コピー」の違いについて解説します
POWER QUERYって何?~エディタ上でのショートカットキー エディタと他ファイルを並行して開く方法も含めて、操作を早く行う方法を解説します
POWER QUERYって何??~時刻を曜日と日付に変換 時刻データを曜日や、日付データに変換する方法を解説します
POWER QUERYって何??~経過時間を一括で計算する ワークシート関数では、直接計算できない経過時間を簡単に計算する方法を解説しています
POWER QUERYって何??~日付を月や四半期単位に変換~ 日付データを簡単に月や四半期単位に変換する方法を解説します
POWER QUERYって何??~読込んだデータに行番号追加~ シートに読込んだクエリに行番号を追加する方法を解説します
文字列を記号毎に分割し、行方向に展開する方法 コンマなどの記号をもとにして文字列を分割しつつ、列方向ではなく、行方向に展開する方法を解説します
POWER QUERYって何~既存クエリのステップを一括で削除する方法 適用してステップの欄で一気に複数ステップを整理する方法を解説します
POWER QUERYって何?12~重複のないリストを一瞬で作成 一気に重複を排除したリストを作成する方法を解説します
POWER QUERYって何??11~ユニークな数の集計~ パワークエリならではの集計方法を1点だけ解説します
POWER QUERYって何?~ピボット解除(縦横並び替え)と入れ替えの違い~ 紛らわしい「ピボット解除」と「行列の入れ替え」の違いについて
POWER QUERYって何??9~WEBページからデータ取得~ Webページからエクセルデータを直接取得する方法を解説します
POWER QUERYって何8???~PDFファイルをエクセルに変換~ PDFファイルからエクセルデータを直接取得する方法を解説します
POWER QUERYって何?~列の結合/複合キー活用~ 複数の列を結合して複合キーを作成・活用する方法を解説します
文字列から空白を取り除く_POWER QUERY 文字列の前後や、文字列間に入り込んでいる空白を取り除く方法を解説します
パワークエリエディターで行コピーを行う方法 M関数を使用して行をコピーして複数にする方法を解説します
MICROSOFT POWER QUERY~ACCESSとEXCEL連携~ 特殊な方法でAccessと連携する方法を解説します
POWER QUERYって???合計を計算する グループ化による合計金額の計算方法を解説します
POWER QUERYって何???連番作成 連番を作成する方法を解説します
POWER QUERYって何??パワークエリの使用メリット パワークエリを使うメリットの一つは履歴の管理が便利な点があげられます
POWER QUERYって何???~ACCESSとエクセルの連携~ 通常の方法にてAccessと連携する方法を解説します
ピボット解除を複数列を軸にして実行する 複数列を軸にして縦縦並びを縦横並びに変える方法を解説します
IF 式の中でOR条件とAND条件を使う方法~POWER QUERY~ ANDとORの指定を小文字で行うのがポイントです

置き換えのIF式を簡略化する方法~リスト活用、列削除省略~

IF式は便利ですが、作成後のメンテナンスが大変だったりします

1つ内容を追加するだけでも、or以降のコードを追加しなくてはなりません

今回は、リストを使ってIF式の作成を簡略化する方法と、IF式自体を列に置き換える方法を紹介します

List.Contains関数

今回はList.Contains関数をIF関数内で活用します

このM関数はリスト内に該当するものがあれば「TRUE」そうでなければ「FALSE」を返します

上のGIF画像でいえば、リスト{1,2,3}に対して3は含まれているのでTRUE、4は含まれていないので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はとにかく便利ですので、ついついクエリのステップが長くなったり、後でメンテナンスが面倒になったりするケースもあります

今回のように、工夫すれば簡略化できますので、ぜひ活用してみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

M関数を使用して、可変で出現する特定文字の下の行を切り捨てる

以前、出現する特定文字の上の行を可変で削除する方法は過去の記事で紹介しました

今回は「特定文字の下」を削除する方法を解説したいと思います

上の画像に「部門」という文字が出現しますが、こちらの文字以降の行を可変で削除します

「部門」の文字が5行目に現れようが、100行目が現れようが、それ以降の行を削除できるようにします

ポイント

今回のポイントはM関数「List.PositionOf」を使用し、特定文字の行位置を取得することです

そして、行位置を取得するM関数は数式バー内に仕込むのもポイントです

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の第二引数に「5」と入力されていたところを、List.PositionOfに置き換えます

これで、特定文字で行削除を行う仕組みの完成です!

<まとめ>

今回は、List.PositionOf関数を使用して、特定文字列から下の行を可変で削除できるようにしました

内容を最初聞くと、難しそうに感じられる方もいらっしゃったかと思いますが、ダミーで行削除したステップの数式バーに関数を埋め込むだけなのでとても簡単です

記録マクロを修正するような内容でも、とても本格的なことができるのがPower Queryの良さですね!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【Power Query豆知識】ちょっとしたM関数の書き方の工夫でステップ数を削減する方法

どうしてもクエリが長くなってしまい、後で見返しても内容がよくわからず困っているという声をよく聞きます

今回は、M関数の書き方を工夫してステップ数を削減する「ちょいテク」を紹介します

カスタム列の追加時

カスタム関数を追加した後に、形式を行うことがあります

下の画像では、上の画像のランク列をテキスト形式に変更しています

このやり方だと、形式変更のステップが追加されてしまいます

このケースの場合は、カスタム列を作成したステップに戻り、数式バーに一文を足します

足した一文はこちらです

,Text.Type

この一文を足すだけで、カスタム列の追加と形式変更(テキスト形式への変更)が同一ステップで行えます

では、整数形式に直す時にはどうコードを書くのか?

いちいちすべてコードを覚えておく必要はありません

下の画像は整数形式に直した時の数式バーの記述です

一度、整数形式に変更するステップを追加した後に「コードを把握」し、カスタム列の追加ステップを修正した後、形式変更のステップを削除すればいいのです

列の削除

ある列を削除した後に、別の列を削除したくなることはよくあります

この時は、最初に列を削除したステップに、2度目の内容を追加します

下の画像は1度目に列を削除した時の数式バーの内容です

こちらの波括弧:{}内に2度目に削除した列名を追加します

これで、2度目に列を削除したステップは不要となります

列名に空欄が入っている時は注意が必要です

この時は列名をコピーした方が早いです

<まとめ>

今回は、ちょっとした長いクエリを短くする工夫を紹介しました

今回の内容で一気にステップ数が減るわけではありません

ただ地道に工夫を積み上げると成果には確実につながりますので、ぜひ今回のような工夫を積み上げるようにしましょう

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

UDEMYの動画コース「Power Queryを極めたい人必見!!M関数講座」の無料・勉強会の開催報告

昨日、リリース済みのUdemy動画コース「Power Queryを極めたい人必見!!M関数講座」の無料勉強会を開催しました

参加して頂いた皆様には感謝の一言です!

今後の動画コースの内容、並びに、Power Queryの技術改善に多くの示唆を得ることができました!

今回の勉強会では2点「let式を使用した複数ステップの集約」「クエリの関数化」に絞ってお話させて頂きました

長いクエリの対策

Power Queryは便利なので、Power Queryに慣れてくるとどうしても適用したステップが長くなります

ステップが長くなると、後で見返してもどこがどうなっているのかわからなくなったりします

この点が、皆でPower Queryを共有して使う時にも問題となります

今回、日常的に業務でPower Queryを使用している参加者の皆さんと議論させて頂き「長いクエリ」への対策が必要なことがよく認識できました

そして「let式を使用した複数ステップの集約」の技術が長いクエリへの対策に役立つことも、参加者の皆さんに意見や感想をお聞きしてよく認識できました

下の画像のように、カスタム列作成画面でlet式を使用すれば複数ステップを1つのステップに集約することができます

しかも、コメントなども入れれば処理が複数ステップに分かれている場合よりもステップの意図がわかりやすくなります

let式の文法に慣れる必要がありますが、この方式を積極的に使えば、長くクエリをシンプルにわかりやすくできます

クエリの関数化

「販売単価の列」「生存原価の列」と「販売個数の列」を乗算し「利益を計算する列」を作成するなどのカステム関数はこのブログでも紹介しました

今回解説したのは、クエリ自体を関数化する方法です

通常のカスタム関数では各行にて1つの値(販売単価、製造原価、販売個数⇒利益)が出力されますが、クエリの関数化の場合には値の代わりにテーブルが算出されます

今回紹介したのは、ファイル内のテーブルを取得するクエリの関数化ですが、思った以上に使い道がありそうです

複数ファイルを取得するのは、フォルダ内の複数ファイルを取得する方法でも行えます

参加者からおききしたのですが、この方法だとクエリが重くなり、フォルダからファイルを幾つか抜き出すケースもあるそうです

確かにフォルダ内のファイル一括取得は便利ですが、日常業務では確かに頻発しそうな問題だと感じました

今回紹介したデータ取得するクエリの関数化ではフォルダ内のファイルを細分化して取得することができるので、クエリが重くなる可能性を低くできます

データ取得以外にもクエリの関数化の用途がありそうなので、今後も積極的に研究していきたいと思います

<まとめ>

今回は本当に貴重な意見をいくつもお聞きすることができました

今後もユーザーの方との交流しながら、自分自身のスキルアップを図っていきたいと思います

Udemyの動画も頂いた指摘を基にして修正を行っていきたいと思います

⇒Udemyの動画・詳細はこちらから

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】パワークエリを使用して月間スケジュール表を作成する

 今回は、開始時間・終了時間・担当者を可変で指定できる月間スケジュール表(記入用)を、Power QueryM言語の技術をフル活用して作成します

4月1日であれば、13時から15時まで1時間刻みで行が自動で用意されます

担当者も可変にできます

一番のポイントは「開始」「終了」時間の判定列を、日付(1~31)と時刻(0:00~24:00)を網羅した表に作成することです

しかも判定用の列は2列用意します

判定列を作るためには、複数のクエリを用意し、クエリ内にキー列を作成しておくこともとても重要です

キー列:yyyy/MM/dd hh:mm

内容的に大きめの内容なので、今回はポイントの羅列で失礼させて頂きますので予めご了承ください

Power Queryでここまでできるのか?という点を感じて頂けたら幸いです

今回の内容の参考記事についても事前に紹介させて頂きます

複数データの組み合わせ/クエリのマージ~中級編1回目~

見出しがセル結合により2行になってしまっている表をデータ活用1~上級編2回目~

IF式を組み合わせて列作成~上級編10回目

M言語に慣れる_4回目~特殊テンプレート作成~

【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する

変換した列の追加~中級編10回目~

ポイント

各種データの用意

今回、想定する作業は以下のスケジュール表から、必要な時間だけ記載され、担当者も記載された完成スケジュール表を作成することです

スケジュール表

下の画像が完成スケジュール表です

完成スケジュール表

実際にPower Queryのクエリを作成する前に、3つの表(日付データ、時刻データ、担当者)を用意します

日付データと時刻データについては、あとで組み合わせて、次のようなクエリ(日付時刻クエリ)を作成します

こちらの結合済みの列は後でスケジュール表とマージして「開始時間」「終了時間」を紐づけるためのキー:yyyy/MM/dd hh:mmになります

日付データ

4月のスケジュールであれば、1日から30日までを用意します

*ホームタブの連続データの作成を活用するとデータの用意が早いです

時刻データ

どの月かは別にして0:00~24:00までを用意しておきます

こちらは固定になります

担当者

こちらも用意します

日付時刻クエリとスケジュール表クエリのマージ

前述のように日付と時刻を組み合わせて作成した日付時刻クエリはスケジュール表クエリとマージして、開始時刻と終了時刻を紐づけます

マージするためのキーはyyyy/MM/dd hh:mmになります

スケジュール表をマージするためにも、事前にスケジュール表はピボット解除を行っておきます

そして日付と時刻の列はマージして「yyyy/MM/dd hh:mm」のキー列を作成しておきます

フィル機能の活用

キー列をもとにして、2つのクエリをマージするとキー列に「開始時間」「終了時間」を紐づけることができます

但し、開始時間と終了時間を判定するには、判定列が1列だけでは不十分です

1列だけだと、終了⇒開始(上の画像だと16時以降)までの時間帯が判定できないからです

フィル機能を使って、下に埋めた列を作成します

2列あれば、残すべき行を特定できます

IF式

判定列を利用して、残すべき行を判定します

判定2が開始の場合には行を残します。判定1と2が終了の場合にも残します

*1の場合は行を残す、0の場合は行を削除する

逆にそれ以外は行を残しません

担当者の紐づけ

担当者はカスタム列を作成してリストで紐づけを行います

*担当者はクエリ名です

リストで紐づけを行い、展開した状態が下の画像の状態です

ピボット処理

担当者の列の内容は、下の画像のように見出しにします

この表の値欄は空欄になるので、ダミーの列を事前に追加しておくのも大きなポイントです

その後にピボット処理を行います

最後に

以上が今回のポイントなのですが、作成していくと、ところどころで並び順が変わってしまうと思います

その際には、インデックス列を追加して調整を図ったください

今回は長文に最後までお付き合い頂き誠にありがとうございました

参考までに今回のサンプルファイルを添付します

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ