シート上からデータソースを変更する方法～上級編14回目

　Power Queryのクエリと連携するファイルを、まるでスマホを操作するかのように簡単に変更できたらいいですよね！

　Power Queryはとても便利なのですが、エディタを開かないと変更ができないのが少々面倒です

前回は「パラメーターの管理」の画面から変更をかける方法を解説しました

　今回は、エディタを完全に開かずに、クエリのデータソース(データ取得元）を下のGIF画像のようにセルの値をもとにして変更する方法を解説します

まず今回のポイントを2点だけ解説します！

目次

今回のポイント

１．セルに名前を付ける

２．データソースの確認（ファイルの取得）

３．名前の組み入れ

４．コードの修正

＜まとめ＞

今回のポイント

A.名前の活用

今回使うデータは、フォルダ名とファイル名がリスト化されています

リスト化されている２つのセルは、左のセルで名前をつけます

B.M言語の使用

　要はコードを書くということなのですが、後で添付するテキストをコピペして使い回した方が効率的です　

A.にて名前を付けたセルの値は可変です

こちらの名前を、「ファイルデータを取得するクエリ内」のコードの中に組み入れます

使用する画面は、過去の記事でオリジナル関数を作成した画面と一緒です

名前を通じて、セルの値を間接的にコードの中に組み込む形にするということになります（例：名前：フォルダ名➡コード：FilePath➡FilePathをデータソースの中のコードへ組み込み）

　では、下の画像のフォルダ内のファイルから「データを取得するクエリ/クエリ名：Dataが既に存在する」という前提にて本格的な解説をはじめます

　上の画像では、フォルダ名が「Folder1」となっていますが「Folder2」も別途存在するものとして解説を行います

１．セルに名前を付ける

ポイントA.でも前述したように、可変のセルに名前を付けます

まず、名前を付ける範囲をカーソルで指定します

名前を付けるのはセルの値に対してですが、名前自体もセルから抽出するので「2行2列」をカーソルで指定します

次に下の図の、黄色の各箇所をクリックします

後は、OKボタンを押せば２つの箇所に名前が設定されています

２．データソースの確認（ファイルの取得）

では、既に作成してあるファイルデータを取得するクエリの中味を確認しましょう

前述の通り、「Folder1」からファイルを取得しているのが分かります

前回は、こちらの画面に別途作成したパラーメーターを組み入れました

今回は別の画面で「組み入れ」作業を行います

まず下の「詳細エディター」をクリックします

すると、コードを修正する画面が開きます

上の画面のコードを、前述の画面と比べてみましょう

画像が小さくて分かりずらいかもしれませんが、コードの内容は一緒です

今回は、１．で作成した２つの名前をまずは組み入れます

３．名前の組み入れ

ここからは、ぜひ以下のテキストファイルからコードをコピペして活用してください

上級編14回_コード

1 ファイル 0.60 KB

ダウンロード

１．で作成した２つの名前を、２．で確認したコードの中に組み入れるコードは以下の通りとなります（名前はフォルダ名、ファイル名でそれぞれ作成しました）

＊＊＊＊＊＊＊＊

・FilePath = Excel.CurrentWorkbook(){[Name=”フォルダ名”]}[Content]{0}[Column1],
・FileName = Excel.CurrentWorkbook(){[Name=”ファイル名”]}[Content]{0}[Column1],

＊＊＊＊＊＊＊＊

注意点としては4つあります

・{0}は1行目という意味になります（Power Queryは0からカウントします）

・コンマをコードの終わりに必ず入れてください

・FilePathとFileNameはご自身で使いやすい名前に変えて頂いて構いません

・こちらの「Name=”フォルダ名”」名前指定の箇所はダブルクォーテーション：””をつけてください

４．コードの修正

３．でFilePathとFileNameに「名前」を通じてシートの値が設定されました

では、このFilePathとFileNameを２．で確認したデータソースのコードに組み入れます

＊＊＊＊＊＊＊＊

ソース = Excel.Workbook(File.Contents(FilePath&FileName), null, true),

＊＊＊＊＊＊＊＊

組み込みが終了すると、「適用ステップ」に組み込んだ内容が下のGIF画像のように表示されます

これで、エクセルシートに読込処理を行えば処理は終了です

ちなみに、エクセルシート上で値を変えても、更新処理を行わないと表示内容は変わりません

ただし、更新処理を行うと列幅がテーブルに合わせて変わってしまいます

そうすると、全体が不格好になってしまいますので次の箇所を変更します

データタブ⇒プロパティとクリックすると「列の幅を調整する」のチェックを外せるようになります

これで、更新前に設定した列幅が維持されます

＜まとめ＞

　今回は、セルの値をパラメーターとして設定し、セルの値を変更すればデータ取得先のフォルダとファイル名を変更できる仕組みについて解説しました

　仕組みとしては大きく分けて２つありました

①セルの値を名前で登録する

②詳細エディタで①で設定した名前をコードの中に組み入れる

　この２つの処理を通じて、セルの値を間接的にデータソースを取得するコードに反映できるようにしました

　コードについては、まずはコードの意味自体を理解するより「どこにコピーを活用するか？」「どこは手動で直さないといかないのか？」を理解するのが重要です

　今回、解説したような[]{}が度々出現するコードの規則性を理解するのはかなり困難です

一方で、次回の解説でも当てはまりますが「コピペ」で十分活用できます！

今回は以上です

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

次回もエディタを開かずに変更を行う方法について解説をします！

にほんブログ村