カテゴリー別アーカイブ: Power Query

置き換えの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をクラウドで活用する方法~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/DataVersexPower Auttomeの組み合わせをすれば、クラウド上でPower Queryを活用できます

データフローの作成

まずはテーブル画面からインポートを指定します

次にデータソースを「Excelブック」で指定します

データソースを指定したら、OneDrive内のファイルを指定します

次に取得元のテーブルを指定します

ここからはPower Queryエディターが立ち上がります

エクセルでPower Queryエディターを操作する場合は、クエリを作成後、読み込み処理を行います

Dataverseの場合には、読み込みではなく、既存のテーブルへのマッピング(列の割り当て)を行います

ただし、作成済みの既存のテーブルにマッピングするのではなく、全く新しくテーブルを作成する場合には「新しいテーブルに読み込む」を指定すればOKです

ちなみに「読み込まない」を指定して、データフロー上だけでテーブルを使用することもできます

Power Automate

こちらでは、要点を抜粋して記事を書かせて頂きます

データフローを更新するには、「データフローを更新する」アクションを使用します

データフローを更新した後に、OneDrive上にデータフローで更新したテーブルの内容をコピーするには、まずは「行を一覧にする」アクションを使用します

一覧にしたテーブルの各行は、繰り返し、OneDrive上のエクセルに追加します

<まとめ>

今回は、クラウド上でPower Queryを活用する方法を解説しました

Power AppsではPower Queryエディターを使用できるのでとても便利です

今回の記事ではPower Automateに関しては、要点を抜粋して記事を書かせて頂きました

実際には、使用したい内容に合わせて調整が必要です

例えば、データフローを更新すると、自動的にデータがテーブルに追加されます

ですので、時には、テーブルを削除するという対処方法が必要な時もあります

後、データフローを更新した際に、待ち時間を設定する必要も時にはあるかと思いますので、この点を意識しておく必要があります

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

にほんブログ村

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

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

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

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

カスタム列の追加時

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

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

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

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

足した一文はこちらです

,Text.Type

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

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

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

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

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

列の削除

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

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

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

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

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

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

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

<まとめ>

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

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

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

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

にほんブログ村

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

(続編)2つの表にてどの行が追加、削除されている? ⇒どの列が変更になっている?

前回の続きで、パワークエリを使用してだけでなくについても、の変更もあわせて変更箇所を特定できるようにしたいと思います

ポイント

2つの表を結合するのは前回と一緒です

今回はピボット解除を行い、複合キーを作ったうえで結合を行います

使うデータ

Before、Afterの2つの表を用意するのですが、キー(りんご、みかん・・)を行単位で作成し、列は月単位で作成します

最後は2つの表でどこが変わったかを明確にできるようにクエリを作成します

ピボット解除&複合キー作成

BefoerとAfter、それぞれのクエリはピボット解除を行い、縦横並びを縦縦並びに変えます

その後、「列のマージ」を使用して、複合キーを作成します

この複合キーを使用して、BeforeとAfterの2つの表を結合(マージ)します

マージ処理

ポイントでも前述したように、2つの表を複合キーを使用してマージ処理します

マージ種類は通常でいけば、完全結合がよいです

「完全外部」の結合であればAfterの表にて追加・削除されたものが全て網羅できます

但し、こちらは用途にあわせて6つの結合種類を使いわければよいかと思います

各種調整

判定列の追加

マージ後は、条件列を使用して〇×を判定する列を追加することができます

複合キーの分解

マージ後は複合キーを分解して必要な情報だけ抜きだすこともできます

複合キーの分解は「抽出」の「区切り記号の後のテキスト」を使用し、複合キーから「月」を抜き出します

そうすると、BeforeとAfterの比較が行いやすくなります

<まとめ>

今回はマージを更に有効活用して、行だけでなく、変更があった列と値を特定できるようにしました

ポイントはピボット解除と複合キーの作成により、2つの表を比較しやすい形で結合することです

ぜひ有効活用してみてください

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

にほんブログ村

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

2つの表にてどの行が追加、削除されている?~結合の6種類~

2つの表の間でどの行が追加されているのか?削除されているのか?を適時確認・集計できるととても便利です

受注のキャンセルデータの管理やミスの発見など、2つの表の適時確認・集計の活用の場はかなり広いです

Power Query結合(マージ)には6種類が用意されていますので、それぞれを使い分けるとかなりの集計上手になれます!

アイコン

結合サンプル 67.09 KB 4 downloads

...

今回使用するデータ

今回は1から始まる連番で管理された2つの表を使用します

結合(マージ)

結合の種類は下の画像の赤色の箇所で選択・指定できます

左外部

左の表の全てと、右の表にて左の行と一致する行を結合します

左外部

右外部

右の表の全てと、左の表にて右の行と一致する行を結合します

右外部

完全外部

一致する一致しないに関わらず、2つの表に存在する行を網羅します

右の表で追加された行や削除された行が一目でわかるようになります

完全外部

内部

2つの表の間で一致する行のみ結合します

内部

左反

左の表から右の行と一致しないものを抽出します

右の表にて削除された行が一目でわかるようになります

左反

右反

右の表から左の表の行と一致しないものを抽出します

右の表で追加された行が一目でわかります

右反

<まとめ>

Power Queryの結合にて一つ問題になるのは、表現がわかりにくいことです

「最初の行」とは左にある表、「2番目の行」とは右にある表と置き換えればわかりやすいと思います

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

にほんブログ村

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

複数グループ内で値違いの箇所を見つける~グループ化の応用~

先日ある方から大量の商品データの中から、価格違いの設定が起こっているところを見つけたいという依頼がありました

またルールがあり、同じ素材/同グループであれば販売する色種類が違っていても同じ価格で設定しなければいけないということでした

もちろん、目でみて判断することもできますが、大量データがある場合にはとても困難です

こういった場合はPower Queryグループ化機能で簡単に価格違いが起こっているグループを見つけることができます

今回は次のデータを使って解説を行いたいと思います

ある商品を価格設定するときに、地区内では同じ価格設定にしなければならないものとします

こちらのデータから価格違いが起こっている地区を見つけます

ポイント

Power Queryのグループ化機能では、グループ化の方法として「最小」「最大」がありますので、こちらを活用します

グループ化の適用

解説はPower Queryエディターからはじめさせて頂きます

ちなみに、上の画像では元のエクセルデータにフィルを適用していますので空欄が埋まっています

上記の画像の状態からまずグループ化をクリックします

デフォルトでは「基本」設定になっていますが、今回は「詳細設定」を指定します

こうすることで、「集計の追加」ボタンにより、グループ化の集計方法を「最小」に加えて「最大」も指定することができます

グループ化を実際に適用すると下の画像のような状態になります

グループ内にもし価格違いがあると、上の画像の黄色の箇所のように「最小:1400<>最大:1500」となります

条件列の作成

ここからは条件列を作成して、最小と最大が違うときは「X」そうでなければ「」という表示をする列を作成します

条件列は次のように設定します

価格違いがあるグループと明細リストの抽出

上の条件列を作成すると下の画像のような状態になっています

もちろん、「×」の箇所だけフィルターをすれば、グループ内で価格違いが起こっている箇所は分かります

ただ、下の画像のように店舗と価格もリストにしたいとします

目で元のデータをフィルターする方法もありますが、M関数を使い、自動的にフィルターする方法もあります

まずは下の画像の状態で、適用したステップを「myList」とするところから解説を始めたいと思います

後でこちらの「myList」を参照します

次に

関数マーク/fx*をクリックしたステップを追加した後に、元の「フィルした状態/下方向へコピー済みステップ」を参照します

*参照ステップの記事を参照

これで、グループ化を行う前の状態に戻りました

ここから「地区」列がmyListに含まれるかどうかを判定する列を「カスタム列」作成画面で作成します

List.Contains関数を使うことで、地区の列の値が「myList」ステップの地区列に含まれるかどうかを判定できます

ここから「TRUE」のみをフィルターすればリストは完成です

<まとめ>

今回は、グループ化機能の最小と最大をうまく組み合わせて、グループ内の違う価格設定を発見する方法を紹介しました

ピボットテーブルでも同じことができます

ピボットテーブルの場合には分散や標準偏差を出す方法もあります

グループ内で価格にバラツキがある場合には、分散や標準偏差が0以外になります

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

にほんブログ村

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

【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法

【列方向ではフィルターは行えないと思っていませんか?実は3クリックをプラスすれば可能です】

Power Queryエディタ画面にて、空欄だけの列は処理したくない・と思ったことはありませんか?

実は「ヘッダーの上げ下げ」と「行列の入れ替え」を組み合わせれば可能です

上記のGIF画像では、空欄になっている「」「」の列をフィルターしています

ポイント

一番重要なのは2回にわたる「行列の入れ替え」です

行列を入れ替えれば、列方向のデータを行方向に変換できます

フィルター処理をした後は再度、行列を入れ替えます

処理の実際

行列を入れ替える前にまずは「ヘッダー行」を下げておきます

ヘッダー行を下げておくことで、見出しも2回にわたる行列入れ替え操作の対象となります

行列を入れ替えた後にフィルター処理により空欄行を除きます

フィルター処理をした後は、再度行列を入れ替えます

そのままだと、ヘッダーに本来配置されるべき列名が反映されていません

ですので、1行目をヘッダーに上げます

そうすれば完成です

<まとめ>

一見、列方向ではフィルター処理ができないように思えますが「行列入れ替え」処理を組み合わせれば実は簡単です

列フィルターが行えるだけで特殊なデータ整理が行えるようになります

今回紹介したのは空欄のフィルタでしたが、列名の置き換えなども一括で行えます

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

最後に、ヘッダーの上げ下げを組み合わせることだけは忘れないようにしましょう!

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

にほんブログ村

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

Power Queryならではのタイムテーブル活用方法

Power BIやPower Pivotではタイムテーブルの活用が欠かせません。ただPower Queryでもタイムターブルの作成と活用を行うことができます。今回の記事ではPower Queryならではのタイムテーブルの活用方法を紹介します

タイムテーブルを活用することで、集計作業で必ず必要になる「前期比での対比」「四半期単位への変換」などを簡単に行えるようになります

ボタンのワンクリック、もしくはたった1行の簡単なコード入力だけで、連続した日付が網羅されたカレンダーテーブルを作成できるのは本当に便利です

Power Queryでも空のクエリから1行のコードを入力するだけで、全く同じようにカレンダーテーブルを作成できます

今回は作成したカレンダーテーブルを活用して、担当者のスケジュール表を作成してみましょう

ポイント

別テーブル作成

カレンダーテーブルを作成するのはもちろんですが、カレンダーテーブルとは別に担当者テーブルを作成してマージします

担当者テーブルを作成する時には、マージする際のキーにする列「」を作成するのもポイントです

もちろんカレンダーテーブル側にも同じ内容のキー列を準備しておく必要があります

ピボット

カレンダーテーブルは当然、下の画像のように縦方向で作成されます

こちらはピボット処理によりに方向を変えます

上の画像の各日付の列には空欄が入力されています

この点については詳細を後述します

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

Power Queryのカレンダーテーブルは、空のクエリから波括弧:{}と複数のM関数を組み合わせて作成します

・波括弧:{}⇒..と組み合わせて連続データを作成

#date関数⇒日付データを作成

Number.From⇒数字データを作成

上記の3つを組み合わせてカレンダーリストを作成します

= {Number.From(#date(2022,4,1))..Number.From(#date(2022,4,30))}

リストを作成したらテーブルに変換します

更に日付形式に変換します

担当者クエリの作成

こちらはポイントで前述したように、キー列を設定したうえで作成します

担当者の人数が少なければ、Power Queryエディタ上からの入力も可能です

カレンダーテーブルのピボット処理

前述のカレンダーテーブルには、ピボット処理(縦⇒横)の前に2つの列を追加しておきます

キー列の追加

カスタム列・作成画面にて担当者クエリで作成したキー列と同じ内容の列を作成します

空欄列の追加

こちらの追加処理はとても重要な処理です

この空欄列は、連続日付データをピボット()に並べ替えた際に表の中身となり、スケジュール表の入力欄になるものです

空欄の作成はキー列と同じくカスタム列・作成画面で行います

ピボット処理

ピボット処理は変換タブから行います

上記の「列のピボット」をクリックする前に、連続日付データがある列をカーソルで選択しておきます

「列のピボット」をクリックしたらダイアログボックスが表示されます

こちらで2つ指定します

・値列(表の中身)⇒空欄列

・詳細設定オプション⇒値の集計関数⇒集計しない

上記のピボット処理により、下の画像のような形になります

クエリのマージ

前述の2つのクエリをマージすると、担当者別スケジュール表が完成します

ただキーの列は削除しておきましょう

<まとめ>

今回はPower Queryならではのカレンダーテーブルの活用方法を解説しました

カレンダーテーブルは時系列のデータを集計する上でとても便利なものです

本来はPower Queryではカレンダーテーブルの機能はありませんが、M言語の簡単な仕組みを組み合わせれば簡単に作成することができます({}、..、#date、Number.From)

今回紹介した活用方法は「担当者スケジュール表」でしたが、他にも活用方法があるはずです

ぜひ、自分なりの活用方法を開発してみてください!

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

にほんブログ村

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

【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換

読者の皆さんも見やすくするためにセル結合を組み合わせた、下の画像のような表をみたことがありますよね!こちらの表をPower Queryの基礎技術を組み合わせてテーブル形式にしましょう!

この手の表は見た目がよさそうな気がしますが、データを追加したり、編集するのにとても不便です

「この表はもう加工・編集のしようがない・・」

そんなことはありません、あくまで「ピボット解除、行列入れ替え」「フィル」「列のマージ」「列の分割」などの技術の組み合わせで簡単にテーブル形式に直せます

今回は行列ともにセル結合がある表を扱いますが、行方向のみの場合は過去にも記事を書いてますので、ぜひそちらもご参照ください ⇒過去の記事

今回のポイントは列のマージと列の分割の組み合わせです!

行列を入れ替えるのと、フィルで空欄を埋めるのは行方向のみセル結合がある場合と一緒です!

小計や合計の行列を消去

解説はPower Queryエディタの状態からはじめさせて頂きます

小計の行や合計の列は必要ないので、フィルターしたり列の削除を行い、消去してしまいましょう

下方向のフィル

一つ目のセル結合はフィルで埋めます

フィルは変換タブから行います

下の方向でフィルを行えば、下の画像のようにnull/空欄が埋まります

列の結合

後でデータを扱いやすくするために、部門と四半期の列は「列のマージ」により1列にまとめてしまいます

列のマージは変換タブで行います

列のマージ時には「-」などの記号により、で分割しやすくしやすくしておくのがポイントです

列のマージを行ったことで、表がシンプルに整理されてきました

行列の入れ替え

この後、行列の入れ替えを行いますが、1つ準備が必要です

見出しをデータに降ろす必要があります

変換タブにで「ヘッダーを1行目として使用」をクリックします

下の画像が見出しをデータに降ろした状態です

ここから「関東・・・の行」も含めた形で行列の入れ替えを行います

行列を入れ替えると、再びフィルでセル結合を埋めることができます

条件列&フィル

ここからフィルで一気に空欄を埋めたいところですが、よくみるとnullでなく「列・・・」で埋まってしまっています

ここは条件列を使用して、列から始まる箇所をnullに変換しておきます

これでフィルで空欄を埋めることができるようになりました

フィルで空欄を埋めると、いよいよ最終形が見えてきます

ここからは①行をヘッダーに昇格②ピポット解除を行います

そして最後に列をマージした列を再度分割します

ピボット解除

ピボット解除を行う前に1行目をヘッダーにします

こちらも前回同様に変換タブから行います

これでピボット解除を行う体制になりました

ピボット解除は左2列を軸にして行います

「その他のピボット解除」を行うと最終形の一歩手前です

ここから、前述の通り、列の分割を行います

列の分割は、列のマージを行ったときに「列の間に入れた記号」を指定して行います

列の分割を行えば、これで最終形となります

後は列名などを調整しましょう!

<まとめ>

今回は長文にて行列双方向にセル結合がある複雑な表をテーブル形式にしました

長文ではありますが、使用している技術は基本的な内容ばかりです

ただ、一つ抜けるとすべてが狂ってしまうので、その点だけは注意頂きたいと思います

今回、記事を作成してみて感じたのは「見出し」が無い表は結局、あまり見栄えがよくはなりません

その点は日常的に意識していきたいものです

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

にほんブログ村

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