混合固定列幅の問題を修正する方法に関する Microsoft Power Query チュートリアル

ムダシル:今日は、非常に興味深い問題に取り組んでいきたいと思います。このファイルの問題は、列で区切られて固定されているということですが、Microsoft Power Query を使用してこれを解決する方法がわかりません。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

まず、列を動的に削除するのは簡単ではありませんでした。次に、このレポートには、列幅が異なるテーブルが 1 つあり、次に列幅が異なる別のテーブルがあります。

したがって、先頭に 1 つの区切り文字を動的に適用すると、データをきれいに取得できなくなります。このデータをすべて 2 番目のテーブルから取得し、製品番号を最初のテーブルから取得したいと考えています。また、すべてのテーブルの各行のジョブ番号も必要です。

私は自分で解決しようとしましたが、これはパワークエリに関係しているため、Melissa の助けが必要でした。少なくとも2日はかかるだろうと思っていましたが、彼女はすぐに解決策を思いつきました。

メリッサはこの複雑な問題をどのように解決したかを見せてくれます。ほとんどの人がこの種の問題に対処し、それらを解決する方法を探していると思います。

メリッサ:最初のヒントは、固定長ファイルを見ている場合は、[表示] タブに移動して、等幅オプションをオンに切り替えることができるということです。

固定長フォントであることがわかります。ヘッダー、初期テーブル、サブテーブルも確認できます。これらは私たちが興味があり、抽出したい部分です。

また、数式バーが有効になっていることを確認してください。入力にわずかな変更を加えるために頻繁に使用するため、画面上に表示しておくことは常に良いことです。

CSV ファイルを保存するファイルの場所のパラメータを作成しました。それをステージング ファイルとして取り込んで、ファイルの場所のパラメーターに追加しました。次に、参照を作成し、その参照に基づいて作業を進めます。これが、現在 Microsoft のパワー クエリで検討していることです。

目次

• インデックス列の追加
• Microsoft Power Queryでのバッファリストの作成
• ヘッダーを行から分割する
• Microsoft Power Query での Null と空白の削除
• セクションの分割
• Microsoft Power Query でのテキスト文字列のトリミング
• 結論

インデックス列の追加

通常、このようなファイルの作業を開始するときは、クライアントの要件を知る必要があります。クライアントが何を必要としているのか、何を求めているのかを尋ねます。

この場合、ヘッダーから品目番号とジョブ番号が必要になり、次にその特定のヘッダーに属するすべての詳細が必要になります。

これらを元に戻すには鍵が必要です。ただし、キーが存在しない場合は、インデックス列を追加することをお勧めします。ミニ テーブル アイコンをクリックし、[インデックス列の追加]を選択して、[From 0]を追加します。

後で、0 から始まるインデックスを持つリストを使用してロジックを使用します。インデックスを 0 から開始するということは、実際には同じ行を参照できることを意味します。それ以外の場合は、0 から始まる位置に到達するために 1 を減算する必要があります。

次に、ヘッダーの位置を見つける必要があります。これらのヘッダーはファイル全体で常に繰り返されるため、これは非常に簡単に行うことができます。

まず、この値をコピーしましょう。

新しい空のクエリを追加して貼り付け、これをHeaderIDと呼びます。

サブテーブルに対しても同じ処理を行います。そのテキスト文字列をコピーし、別の空のクエリを作成し、その値を貼り付けます。これは、詳細な行を検索するときに使用する文字列になります。

このプロセスによってこれらのテーブルのいずれかのヘッダーが変更された場合、テキスト文字列の 1 つを変更するだけで、ファイルは再び機能するようになります。

探している文字列を検索するために M コードに飛び込む必要はありません。これをパラメータとして使用するだけです。

これら 2 つのクエリのロードを有効にしてみましょう。

Microsoft Power Queryでのバッファリストの作成

最初に行うことは、Column1 を一度参照してメモリにロードすることにより、リストに変換することです。こうすることで、ファイルを繰り返し呼び出す必要がなくなります。

詳細エディタを開いて最上部に配置します。ユーザー インターフェイスを使用してコードを構築すると、前のステップが参照されます。

コード内の他の場所にバッファー ステップを配置し、変更を加えたい場合、後で手動で作成しているステップを変更するのに役立ちます。

これをBufferListと名付け、Column1 を参照します。これをメモリにロードするには、 List.Bufferステップを追加します。

私の変数は一番上にあります。何度でも参考にさせていただきます。

最初に決定したいのは、ヘッダーの開始場所です。これらのヘッダー セクションを保持し、すべての行の単一の値を取得するためのキーが必要であるためです。これを行うには、カスタム列を追加し、それをHeaderと呼びます。

Column1 がヘッダー ID と等しい場合、インデックス番号を null にしたいと書きます。

その結果、テキストが見つかり、5 と 23 が返されました。

すべての行にわたってその値が必要なので、それを埋める必要があります。右クリックするだけで入力できますが、非常に単純な構文を使用して数式バーに追加することもできます。

この場合、Table.FillDownを追加し、テキスト文字列でどの列 (ヘッダー) を埋めるかを指定しました。

これで、すべての行に情報が入力されました。すべてのヘッダー セクションとすべての行セクションがこの値を共有しているため、すべてのセクションにキーがあります。

ヘッダーを行から分割する

次のステップでは、行からヘッダーを分割します。別のカスタム列を追加し、それをTempと呼びます。今回は、より複雑なことを行い、以前に作成したBufferList を活用します。

いくつかのリスト関数を使用して各位置を調べ、インデックスに一致するものがあるかどうかを確認します。

if ステートメントから開始し、 List.Containsを使用してBufferList内の特定の位置を検索し、クエリHeaderIDを参照します。

ファイル全体にわたってそれを検索し、リスト内の項目の位置を返したいと考えています。インデックスと一致する場合、その特定の行が一致したことになります。

次に、ヘッダーを識別する値を返したいと思います。この場合、H を返すだけです。最初から書き直す必要がないように、構文をコピーします。

行セクションも識別する必要があります。リストにHeaderIDではなくDetailID が含まれている場合は、行セクションにいます。

Column1が空のテキスト文字列の場合は、空のままにしておきます。そうでない場合は、nullにしたいと思います。

この反対派はヘッダー行を取得して H を返し、次に詳細行を見つけて R を返しました。その後、その行セクション内で共有されているすべての項目に対して 0 を返しました。

これらの空白または NULL は、入力できるため重要です。Fill down はこれらの空白セルを越えて移動しないので、後でそれらを削除できます。

これを数式バーで行い、Table.FillDownを再度使用します。ここでは、 Temp列である列名を含むリストが必要です。

これで、H 値と R 値がこの列全体で繰り返されるようになりました。これは、実際に詳細セクションからヘッダーを分割できることを意味します。

コードを記述したくない場合は、ユーザー インターフェイスから入力することもできます。右クリックして[塗りつぶし]を選択し、[下へ]を選択するだけです。

Microsoft Power Query での Null と空白の削除

これが正しくなったので、不要なものを削除できます。null または空白が含まれる行はすべて不要な行であり、削除する必要があります。フィルタリングすることでそれらを取り除くことができます。

セクションの分割

これらの空白と null を削除すると、必要なものがすべて残ります。この時点では、セクションを分割するだけです。ヘッダー行に焦点を当てて、ヘッダー行を選択することができます。ヘッダー行にはすべての詳細行とは別の間隔があるためです (詳細行にも別の間隔があります)。

数式バーに新しいステップを追加して、同じ列に別のフィルターを作成できるようにします。この場合、ヘッダー セクションをすべて保持します。

これで、ヘッダー行がすべてここに表示されました。

Column1 を選択し、数式バーに移動し、[列を分割]を選択して、位置ごとに分割します。

次に、power query 自体にこれを解決させます。いくつかのポジションを提案します。[OK]をクリックしてこれらのポジションを受け入れます。

ヘッダーから関心があるのは、アイテムとジョブ番号だけです。

ここの数式バー内で、ItemとJob #の名前を変更できます。これにより、列の名前を変更する別の手順を省略できます。

このステップの後は、 Itemを選択し、Job #を選択し、そしてもちろんヘッダー キーを選択するだけです。次に、他の列は不要になったのですべて削除します。

これが結果になります。値をクリーンアップし、テキスト項目とダッシュを削除する必要があります。私たちが必要とするのは、その間の値だけです。

そこで、これを開いてダッシュと項目の選択を解除します。

これですべてのヘッダーが完成しました。

DetailIDについても同じ処理を行う必要があります。後で簡単に戻ることができるように、これらのステップの名前を変更する必要があります。

開始した最初のクエリに戻ります。まず、[適用されたステップ] ペインのフィルターされた行から始めました。

これをコピーしてフィルターに追加します。今回はHではなくRを選択します。

次に、Column1 を選択し、Split 列に移動して位置ごとに分割し、パワー クエリで計算させます。

これがパワークエリが示唆するものです。これを試してみましょう。

これは実際にはかなり良いようです。合計行でも完全に分割されています。もちろん、インデントがあったのでスペースがたくさんあります。

Microsoft Power Query でのテキスト文字列のトリミング

最初の列を選択し、Down + Shift キーを押して列 1.10 まで選択します。「Transform」に移動し、「Format」を選択してから「Trim」を選択します。トリミングでは、文字列の前または末尾にある余分なスペースのみが削除され、文字列間の余分なスペースは削除されません。

次に、ヘッダーをプロモートするだけなので、これらの列のすべてのヘッダーやタイトルを入力する必要はありません。分割ステップでは、2 つの列の名前を変更しました。もちろん、列が 10 個あると、少し面倒になります。

それらの過剰な価値観も取り除く必要があります。合計があるため、これらの最後の 3 列のいずれかを使用する必要があります。これらの列は、その間に追加の値を持つ唯一の行であるためです。次に、これらの空白、ダッシュ、テキストの選択を解除します。

次に、不要な列を削除して、ヘッダーと詳細だけを含むテーブルが残ります。これらのセクションを再び結合するにはキーが必要です。

そのために、自己マージを使用して、テーブルをそれ自体とマージして、その情報を元に戻すことができます。[ホーム]タブで、[結合]を選択し、列 5 と同じクエリを選択します。

AllDetails の代わりに、マージする最初のテーブルとして AllHeaders が必要です。

これにより、ヘッダー テーブルからすべての情報が返され、各アイテムと各ジョブ番号が 1 行で表示されます。

キーを使用して詳細行と結合しました。ここの空白部分の横を押すと、ヘッダー 5 に属するすべての行のプレビューが表示されます。

ここで最後の列を削除すると、Microsoft パワー クエリの混合固定列幅の修正が完了します。

結論

このチュートリアルでは、Microsoft Power Query を使用して固定列幅が混在している問題を解決する方法を考え出しました。この特定のチュートリアルで取り上げたコンテンツを気に入っていただけた場合は、LuckyTemplates TV チャンネルに登録することを忘れないでください。

私自身やさまざまなコンテンツ作成者からの膨大な量のコンテンツが常に公開されており、全員が LuckyTemplates と Power Platform の使用方法を改善することに専念しています。

メリッサ