Power Query(M言語)入門
「売上」が数値の行のみ取り込む

Power Query(パワークエリ)とM言語を練習問題を通して学んでいきます
公開日:2023-02-13 最終更新日:2023-02-17

「売上」が数値の行のみ取り込む


「パワク7本目」
Power Query(M言語)の問題と解答・解説


本シリーズは、ツイッターと連動企画になります。
ツイッターで問題を出して、それに解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。

パワク7本目

Excel エクセル Power Query M言語

#パワク7本目
CSVをパワクで取り込みます。
列「売上」が非数値および空の行を削除して、「売上」が数値の行のみ取り込んでください。
#PowerQuery #M言語


解答コード

Excel エクセル Power Query M言語
let
    ソース = Csv.Document(File.Contents("D:\パワク練習\CSV\test2.csv"),
    [Delimiter=",", Encoding=TextEncoding.Utf8, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"日付", type date}, {"売上", type number}}),
    削除されたエラー = Table.RemoveRowsWithErrors(変更された型, {"売上"}),
    フィルターされた行 = Table.SelectRows(削除されたエラー, each [売上] <> null)
in
    フィルターされた行
若干の変更はくわえましたが、ほぼGUI操作で作成されたままです。
Table.TransformColumnTypes
Table.RemoveRowsWithErrors
Table.SelectRows
以上については後述します。


GUI操作での作成

「売上」列を10進数にします。

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

「現在のものを置換」
続けて、
「エラーの削除」

Excel エクセル Power Query M言語

残るは空白行の削除なのですが、、、

Excel エクセル Power Query M言語

この「空白行の削除」では削除されないようです。
フィルターの「空の削除」を使います。

Excel エクセル Power Query M言語

以上で作成されたスクリブトが以下になります。
let
    ソース = Csv.Document(File.Contents("D:\パワク練習\CSV\test2.csv"),
    [Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"日付", type date}, {"売上", type number}, {"備考", type text}}),
    削除されたエラー = Table.RemoveRowsWithErrors(変更された型, {"売上"}),
    フィルターされた行 = Table.SelectRows(削除されたエラー, each [売上] <> null and [売上] <> "")
in
    フィルターされた行
このスクリブトに若干の修正を加えて解答のスクリブトを作成しました。


Table.TransformColumnTypes

Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table

オプションのパラメーター culture で指定されたカルチャ (例: "en-US") を使用して、パラメーター typeTransformations で指定された列に変換操作を適用する (形式は { 列名, 型名}) ことにより、入力 table からテーブルを返します。
列が存在しない場合、例外がスローされます。

typeTransformations as list
Table.TransformColumnTypes(昇格されたヘッダー数,{{"日付", type date}, {"売上", type number}})
typeTransformations as list
これに、
{{{"日付", type date}, {"売上", type number}}
GUI操作で作成されたままで問題ありません。。
今回は2列あるので、
{{...},{...}}
このように2重の波括弧になります。

optional culture as nullable text
カルチャを指定します。
現在のカルチャは、
Culture.Current
これで取得できます。
指定するテキストは、
"en-US"
"ja-JP"
これらになるのですが、指定可能な一覧が見つかりませんでした。
Windowsで使用されている一番的な「言語/地域」なら大抵指定できるとは思いますが、あまり必要性はないと思います。

Table.RemoveRowsWithErrors

Table.RemoveRowsWithErrors(table as table, optional columns as nullable list) as table

少なくともセルの 1 つにエラーがある行を削除した上で、入力テーブルからテーブルが返されます。
列リストが指定されている場合、指定の列のセルのみでエラーが調べられます。

Table.RemoveRowsWithErrors(変更された型, {"売上"})
{"売上"}を指定しているので、「売上」列がエラーの行のみ削除されます。


Table.SelectRows

Table.SelectRows(table as table, condition as function) as table

選択 condition と一致する行のテーブルを table から返します。

condition as function
つまりfunctionを指定します。
解答のクエリでは、このfunctionに、
each [売上] <> null and [売上] <> ""
このように指定しています。
Eachについて簡単に説明します。

※Learn Power Query M 関数について
このなかで、
each キーワード
each キーワードは、単純な関数の作成に使用します。
“each ...” は、_ パラメーター "(_) => ..." を取る関数シグネチャの糖衣構文です。
each は、既定で _ に適用される lookup 演算子と組み合わせると便利です。
たとえば、each [CustomerID] は each _[CustomerID] と同じですが、これは (_) => _[CustomerID] と同じです。

説明がわかりずらいのですが、
つまり、先のEachは以下のように書き換えることができます。
let
    ソース = Csv.Document(File.Contents("D:\パワク練習\CSV\test2.csv"),
    [Delimiter=",", Encoding=TextEncoding.Utf8, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"日付", type date}, {"売上", type number}}),
    削除されたエラー = Table.RemoveRowsWithErrors(変更された型, {"売上"}),
    フィルターされた行 = Table.SelectRows(削除されたエラー, (_)=>(_[売上] <> null))
in
    フィルターされた行





同じテーマ「PowerQuery(M言語)入門」の記事

別ブックの最終シートの取り込み
列数不定のCSVの取り込み
CSVのA列が日付の行だけを取り込む
A列のヘッダー名を変更する
「売上」が数値の行のみ取り込む
2つのテーブルのマージ
グルーブ内の最小・最大
グルーブ内の最小・最大
有効な最新単価の取得
新旧マスタの差異比較
有効な最新単価の取得


新着記事NEW ・・・新着記事一覧を見る

無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)
PIVOTBY関数(縦軸と横軸でグループ化して集計)|エクセル入門(2023-11-19)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.マクロとは?VBAとは?VBAでできること|VBA入門
10.セルのクリア(Clear,ClearContents)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ