LuckyTemplates でデータをフォーマットする: 不規則なデータ形式に対処する

今日のブログ投稿では、今週の問題 #6について説明します。LuckyTemplates でPower Queryを使用してデータを書式設定する方法を説明します。以下のスクリーンショットは、すべてのデータ変換を完了した後のソリューションです。実際の残高は$685,616.33でした。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

チャレンジ#6にはたくさんのご参加をいただきました。これを達成しようとする皆さんの努力に感謝します。注意すべき点の 1 つは、ほんの少しだけバランスが崩れているエントリーが多数見られたことです。このチャレンジへのエントリーを進めながら、なぜその金額が外れたかを説明します。

でデータをフォーマットする方法に関する私の解決策には複数の手順が含まれており、それらの手順を要約したエントリが多数あることに注意してください。この種のデータを扱う今後の取り組みでは、これらのショートカットを考慮に入れます。したがって、参加者は「今週の問題」の解決を手伝ってくれただけでなく、レポートの作成に関しても長期的には私を助けてくれました。

パワークエリを開いて、どのようにして解決策を思いついたのかを段階的に説明していきます。完成した製品はこんな感じです。

ただし、ソースをクリックすると、これが CSV ファイルを保存したファイルの場所に接続したときに最初に表示されたものであることがわかります。

目次

• コメントの利点
• 金額が間違っている理由
• 列のフィルタリング
• 年齢列の作成
• 結論

コメントの利点

右側の手順を確認してください。Information の略である小さな「i」が見えます。これは、詳細エディターでコメントが作成されたことを示します。

ここで高度なエディターを実際に開いてみましょう。緑色の文字は、その時に考えていたことを思い出すためのコメントであることがわかります。

これには��が過去に行ったことの痕跡が残るため、新しく誰かがそのファイルを見に来た場合、少なくとも私がたどった道筋を知ることができます。

ここでサイドのコメントを見ることができます。Changed Type1に関する最初のコメントは、基本的に、さまざまな列の型の変更に関するものです。ご覧のとおり、私が行ったコメントは「現在の列が 10 進数として設定されていることを確認してください。そうしないと、ここから問題が発生します。」です。

これは、なぜ人々が以前に示した間違った残高金額を思いつくのかという点につながります。

金額が間違っている理由

これらのエントリの 1 つを持っている人からコピーした別の PBIX ファイルを紹介しましょう。$685,615.28は間違った金額であり、この結果の金額を含むエントリが多数ありました。

それがどのように、そしてなぜ起こったのかを考えてみましょう。[変換] タブに移動し、 [データ型の検出]をクリックすると、すべての行が自動的に検出されます。現在の列が整数 64 として表示されることがわかりますが、これは見たいものではありません。

金額が間違っているのはこのためです。このエラーが設定に表示される別の方法もあります。[ファイル]>[オプションと設定]>[オプション]に移動しましょう。

「GLOBAL」領域と「Data Load」セクションの下に、「Type Detection」が表示されます。[各ファイルの設定に従って、非構造化ソースの列タイプとヘッダーを検出する] を選択しました。

最初のオプション (非構造化ソースの列タイプとヘッダーを常に検出する ) を選択すると、残高金額にもエラーが発生します。

の 1 人で、M コードやパワー クエリ言語に関して大きな助けとなっているのが Melissa です。彼女は、このエラーが発生した理由を説明するMicrosoft のドキュメントを見せてくれました。

ご覧のとおり、これは以前は[プロジェクト オプション]と呼ばれていました。[非構造化ソースの列タイプとヘッダーを自動的に検出する] オプションがチェックされています。

このドキュメントには、テーブルの最初の 200 行のみに基づいて計算されるとも記載されています。

私のテーブルでは、元の行数は約 4,000 行程度でした。これが、適切な量を収集できなかった理由です。

Power Query に戻って、LuckyTemplates でデータの書式設定を開始しましょう。Changed Typeステップでわかるように、現在の整数 64 です。

そこで、ここで簡単に行うことは、これらの請求書番号の 1 つでフィルターして、前後の様子を表示することです。

特定の請求書番号を選択すると、現在の金額が 4,741.01 であることがわかります。

この変更タイプをクリックすると、値が変更され、四捨五入されて 4,741 になっていることがわかります。

これは、今週の問題の他のエントリの何が問題だったのかを簡単に説明する方法です。

パワー クエリ M のソリューションに戻りましょう。次のステップでは、行をフィルター処理して空白行のみを表示します。

次のステップでは、列を削除します。スクリーンショットに表示されている列を除くすべての列を削除しました。また、最初に表示されたレコード番号は、実際には請求書番号であることに注意してください。

次の手順では、各フィールドの null である空白行を削除します。

次に、以前は空白だった最初の列の名前を変更し、それを Customer に変更しました。実際の Customer フィールドとなる別のフィールドを追加するためです。

次の手順では、Customer 列の空白の値をすべて null に置き換えます。なぜこれを行わなければならないかというと、次のステップのためです。

これらの行をすべて埋めるには、どの行も空白にすることはできず、NULL にする必要があります。

フィルダウンを行うには、列をクリックしてFill、次にDownに移動します。

このようにして、この列全体にすべての名前が表示されることがわかります。

列のフィルタリング

ここでさらにいくつかの行をフィルターし、請求書番号でフィルターして空白の行を削除しました。

上のスクリーンショットの列をコピーし、名前の前の数字を削除した場所に別の Customer 列を追加しました。

列の順序も変更しました。その新規顧客の列を最後から最初に移動しました。次に、末尾にドットまたはピリオドがある Customer 列を削除しました。これで、左側に数字のない透明な Customer 列が 1 つだけできました。

次のステップは Inserted Sum です。ここでは、Current、1 ～ 30、31 ～ 60、61 ～ 90、91 ～ 120 のすべての値を取得しました。これらの列をすべて加算して、 Addition というカスタム列を取得しました。

これを行うもう 1 つの方法は、すべての列を強調表示し、[列の追加]タブに移動し、[標準] をクリックして、[追加]をクリックすることです。

これにより、パワー クエリに何も入力しなくても、Additionフィールドが表示されます。

そして、元の列 (Current、1 – 30、31 – 60、61 – 90、および 91 – 120) をすべて削除しました。その後、名前をBalanceに変更しました。

次のステップは、カスタム列を追加することです。このチャレンジのために、この元のデータが顧客から取得されたのは 1 月 21 日でした。

日付はテキスト関数として自動的に出てきたので、この手順で日付関数に変更しました。

年齢列の作成

年齢列を作成するために、今日の日付と期限を強調表示しました。次に、[列の追加] タブに移動し、[日数の減算]をクリックします。

次のステップでは、これを整数に変更します。

次に、列の名前をAgeからDays Ageに変更しました。次に、さらに 2 つの条件列を追加しました。1 つは、老化列でこれらの項目を再分類することです。

2 番目の条件付き列は、経過時間列を並べ替えて、順番に表示するのに役立ちます。

結論

これが、この「今週の問題」に対する私なりの解決策を思いついた方法です。[閉じる] をクリックして適用すると、結果は次のようになります。

私と一緒に解決策を楽しんでいただければ幸いです。LuckyTemplates でデータを書式設定する方法に関するこのチュートリアルを気に入っていただけた場合は、同様のコンテンツを視聴できる TV チャンネルに登録してください。

ジャレット