タグ別アーカイブ: 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へ

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

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

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

頂いた意見・指摘

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

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

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

リニューアルポイント

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

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

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

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

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

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

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

https://youtu.be/m53fBnCyJgA

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

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

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

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

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

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

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

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

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

にほんブログ村

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

【パワークエリ・チャレンジ】グループ別の小計と合計を表示する

通常は一律で処理するPower Queryですが、M関数を使えば小計と合計を列に追加・挿入することができます】

M関数をうまく活用することで、通常では考えられないような表を作成することができます

今回は上のGIF画像の左の表から、グループ別の小計と全体の合計を自動的に挿入します

上記の内容ではM関数を使用しますが、その前に大事なのはレコードやテーブルとは何か?どのように作成できるか?ということです

まずは、空のクエリからレコードとテーブルを作成してみます

レコードとテーブルの作成

レコード

レコードは角括弧:[]を使用することで空のクエリから作成することができます

テーブル

こちらはM関数:#tableを使用します

関数内では波括弧:{}を組み合わせて、列名と行の値を指定します

行の値は2重の波括弧:{{}}で指定する点にご注目ください

グループ別の小計の挿入

まずは、小計を行う単位:部門でグループ化を行います

操作は「すべての行」を指定します

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

= 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}と指定することで、各テーブルの部門列の1行目を指定しつつ、”小計”を組み合わせることで「部門名+小計」の文字列を追加します

②は空欄を指定します

③は各テーブルの金額列の小計を指定します

ちなみに上記の数式の右から2番目の)が#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関数とは何か?について学べる内容になっています

ぜひ手を動かして試してみてください

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

にほんブログ村

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

【パワークエリ・チャレンジ】列数が可変の表を組み合わせて計算する

【M関数を利用することで、数式の修正しなくても列数が可変の表を組み合わせて計算できるようにします】

値を別表で作成した上で計算を行うケースはよくあると思います

上の画像のケースでは、ボーナスを計算するのに基礎となる評点を作成した上で各ポイントをクリアしていれば「ポイント別」に加算点を計算しています

例えば、上の画像で言えば田中さんはポイント1と2をクリアしているので、80*9%と80*10%が加算点の合計点になります

テーブル:tableMatrix
テーブル:tablePoint

上記では関数で計算していますが、問題はポイント数が変わった場合です

ポイント数が変わるたびに関数を変更しなくてはなりません

今回の記事では、いくつかのM関数を組み合わせたカスタム列を作成することで、計算の対象列数が可変でも計算が正しくできるようにします


使用するデータ

2つのテーブルを使用します

上の画像は「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クエリの列名をリスト化します

tablePoint

関数の引数はリスト名を指定するだけでOKです

このリストがList.Accumulate関数の第一引数になるということは、計算式(state+current)内の変数:currentには「ポイント1・・・」などのテキストが繰り返し代入されるとこになります

どうやって計算が行われるのか?と不思議になる読者の方もいらっしゃると思います

この点は次のRecord.Field関数の箇所で解説します

Record.Field関数

Record.Field関数は特定のレコード中から特定のフィールドの値を抽出します

ちなみに、上の画像のtablePoint{0}*はtablePointテーブル/クエリの1行目のレコードになります

*M言語では{0}とすることで1行目を表します

今度は第二引数を”ポイント2″を指定してみます

ポイント2のフィールドの値が抽出されます

List.Accumulate関数内では「ポイント1,2・」が繰り返し変数:currentに入ります

この変数:currentはRecord.Field関数の第二引数に設定され、数値が抽出されます

計算式の追跡

では、ここでList.Accumulate関数の計算式を追跡してみましょう

(state,current)=>①state+②Record.Field(tablePoint{0},current)*③Record.Field(_,current)*④[評点]

追跡するのは次の行/レコードです

計算式1:state/0(計算の最初の値)、current/”ポイント1″

①:0(計算の最初の値・第二引数)

②:Record.Field(tablePoint{0},”ポイント1″)=0.09

③:Record.Field(_,”ポイント”)=1

④:[評点]=80

①+②*③*④の計算式により7.2が算出されます

ここで注意点です

③の_はカスタム列を作成しているクエリのレコードをそのまま受け継ぎます

今回の場合は次の画像のレコードを受け継ぎます

計算式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が算出されます

計算式3: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の最後は「,」を付ける必要がありません

逆にそれ以外のステップは「,」が必要となります

参考までに使用したファイルを添付します

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

にほんブログ村

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

置き換えの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へ

IF 式の中でOR条件とAND条件を使う方法~Power Query~

IF式の中にOR条件とAND条件を組み入れて、条件式をうまく構築しよう】

Power Queryのカスタム列ではIF式を活用することができます

過去の記事でも紹介しましたが、IF式を複数ネストすることもできます

今回の記事では、IF式の条件式にAND条件とOR条件を使用する方法を紹介します

ポイントは何気にANDORの大文字と小文字の区別です

OR条件

下の画像には「A,B,C,D,E,F,G」のいずれかが含まれた文字列を含む列があります

「A」または「B」の文字列を含む場合には「true」をIF式で出力してみましょう!

If式の中にはText.Contains関数を使用します

このM関数は指定文字が含まれている場合には「true」を出力します

以下が今回のIF式です

if (Text.Contains([文字列],”A”)=true) or (Text.Contains([文字列],”B”)) then true else false

OR条件は小文字の「or」で指定する点は注意が必要です

AND条件

今度はAND条件にて「A」且つ「B」を含む場合には「true」を出力しましょう

今回もAND条件の指定を小文字にする点は注意です

if (Text.Contains([文字列],”A”)) and (Text.Contains([文字列],”B”)) then true else false

これでAとBを両方含む1行目だけがtrueと出力されました

<まとめ>

今回はPower QueryのIF式内にAND条件とOR条件を指定する方法を解説しました

なるべくIF式はすっきり記述した方がいいので、()を入れて条件式を明確にする工夫などを行って行きましょう

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

にほんブログ村

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