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

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

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

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

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

このような場合はまずは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へ

グループ別に合計や平均に加えて標準偏差を抽出する方法

【数字は平均だけではよく分かりません。グループ化機能を更に深堀し、分析用の列に標準偏差も加えましょう!】

グループ化機能はとても便利です!グループ毎の集計を簡単に行いつつ、集計した数値を並べることができます

但し、グループ化の集計メニューには「標準偏差」が足りません

平均値を集計しても、バラツキが大きいのか、バラツキが小さいのかで平均値の解釈が違ってきます

例えば、上のグラフにはA~Cという3つのグループがあります

いずれも平均は150です

ただ明らかにグループBとグループCでは平均値150の解釈が違ってきますよね

今回はグループ化のラインナップに標準偏差をサクッと加える方法を解説します

グループ化の基本

グループ化を行う際、「基本」ではなく「詳細」を選択すれば複数の集計を行うことができます

今回は「合計」「人数」「平均」を集計した後に「すべての行」を集計に加え、後で集計結果を標準偏差に集計し直します

すべての行によるグループ化

「すべての行」のグループ化により、グループ別にテーブルが作成されます

この各テーブルが配置された列を使用してカスタム列を作成します

カスタム列内では「List.StandardDeviation」という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をクラウドで活用する方法~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へ