Power Automate の文字列関数: Substring と IndexOf
Microsoft フローで使用できる 2 つの複雑な Power Automate String 関数、substring 関数とindexOf 関数を簡単に学習します。
LuckyTemplates の Power Query を使用して欠落している通貨レートを解決する
このチュートリアルでは、LuckyTemplates で Power Query を使用して通貨換算を行う方法を学習します。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。
このブログで説明する方法は、最後に利用可能なレートを使用して通貨レート テーブルの問題を解決するのに役立ちます。
為替レート表に毎日の通貨レートが記載されていない場合の対処方法がわかります。
目次
欠落データの特定
この問題は、Date テーブルに休日または週末のデータがない場合に発生する可能性があります。
レートがなく、それを考慮していない日がある場合、総売上高などの指標は変換されません。
この通貨レート表を見ると、2016 年 1 月のユーロでのレートが示されています。
ただし、27 日と 28 日が欠落しており、レートがないことに注意してください。したがって、マトリックスに移動すると、27 と 28 にはレートがないことがわかります。
欠落している通貨レート メジャーは、ファクト通貨レート テーブルの単純な合計です。
表内の合計 26.6983 には意味がありません。これは、それを超えるすべての通貨レートの合計にすぎませんが、当日のレートであるため合計する必要はありません。
これは、クエリと集計に関係するデータ モデリングの問題であるため、Power Queryで修正できます。これは DAX でも実行できますが、LuckyTemplates の Power Query を使用した方が簡単かつ高速です。
そのため、[データの変換]をクリックして、Power Query ウィンドウを開きます。
これはデータが欠落しているテーブルです。この通貨レート表には、ユーロ、ポンド、ドルが含まれていることもわかります。
そして、これが 1 月 27 日と 28 日の値を含む最後のテーブルです。これは、必要なテーブルまたは出力です。
取得元のソースからデータをインポートし、データ型を変更するだけです。
異なる通貨の分離
次に、複数の通貨をお持ちの場合は、ティッカーごとに通貨を分けてグループ化することが重要です。
グループ化するときは、必ず [すべての行] でグループ化してください。これにより、たとえば [ユーロ] をクリックすると、通貨レートのみがユーロで表示されます。
ここにもポンド通貨と同様に欠落している日があることがわかります。
LuckyTemplates には、カスタム関数の呼び出しと呼ばれるものがあります。これはFillMissingRatesを備えた関数です。
「ユーロ」をクリックすると、展開された表に表示された欠落データを含む完全な表が表示されます。古い料金から新しい料金まで並べられています。
カスタム関数が何をしているのかを理解します。「カスタム関数の呼び出し」の近くにある歯車アイコンをクリックすると、列名と「すべて」列を取得しているソースが表示されます。
そのためには、基本的なテーブルから始めます。変換を実行し、必要な結果を取得し、それを使用して関数を構築します。他のすべてのサブテーブルがあるテーブルでそれを行うよりも合理的です。
たとえば、ユーロに焦点を当てたい場合は、ティッカーまでフィルタリングします。
欠落している日を確認するには、日付テーブルとマージします。
以下は、フィルター処理された基本的な通貨テーブルと DimDate テーブルです。
結合の種類では、完全外部を使用して両方のテーブルからすべての行を取り込むことができます。これにより、不足しているものと一致するレートが表示されます。
それをマージすると、サブテーブルとして完全なテーブルが得られます。
その後、行を並べ替えてから、DimDate を展開して Date 列のみを含めます。
これで、すべてが一致していることがわかります。また、行を古いものから新しいものへ並べ替えると、Null 値を持つ欠落した 2 日も表示されます。
不足しているレートを埋める
Date.1 を除くすべての列にすべての null を入力するので、この順序であることが重要です。
入力すると、NULL 値がユーロと最後に利用可能なレートに変更されたことがわかります。
次に、日付列などの不要な列をテーブルから削除します。
Date.1 列にはすでに日付が含まれているため、Date 列は必要ありません。次に、クロスレートを 10 進数に設定します。
最後に、列の順序を変更して名前を変更し、データ型が設定されていることを確認します。
これで完全な通貨レート表が完成しました。
ただし、これはこのテーブルでのみ機能します。通貨がグループ化されているテーブルに同じロジックを適用する方法を見つける必要があります。
すべての通貨を統合する
通貨が異なる場合、異なるレート表を維持するのは困難です。したがって、それらをマージして 1 つの通貨テーブルを作成する必要があります。
まず、既存のテーブルを使用し、「テーブル」をクリックして「関数の作成」を選択して関数を作成します。次に関数名を入力します。
その後、高度なエディターに移動します。必要のない計算を削除します。この場合は、ソースを削除し、フィルターし、構文を変更します。
次に、パラメータに「Source」という名前を付け、「Let」と入力します。Filtered Rows が削除されたため、Merged Queries 構文内の値を Source に変更します。その後、構文の最後にあるソースを削除し、「完了」をクリックします。
次に、「列の追加」に移動し、「カスタム関数の呼び出し」をクリックします。「All Data」のような列名を入力します。関数クエリの場合は、以前に作成した関数を選択します。ソースとして「すべて」を選択し、「OK」をクリックします。
次に行うことは、「すべてのデータ」を除くすべての列を削除することです。次に、その列を展開し、「元の列名をプレフィックスとして使用する」設定のチェックを外します。その後、「OK」をクリックして列のタイプを変更します。
Date、Ticker、Crossrate 列のタイプをそれぞれ Date、Text、Decimal に設定します。完了したら、「閉じて適用」をクリックします。
これで、日付とレートが欠落していない組み合わせの通貨レート表が得られます。
データモデル内での関係の作成
データ モデルでは、FactCurrencyRatesテーブルを確認できます。次に、そのテーブルと DimCurrencyRates および DimDate テーブルを使用して、Ticker to Ticker および Date to Date という関係を作成します。
マトリックスで通貨レートの欠落なしを取得すると、27 日と 28 日のレートがすべて Power Query で行われたことを確認できます。
「最後に報告された通貨レート」列には、欠落した日のレートも表示されます。同じレートと値が表示されます。ただし、それらの違いは、この列が DAX で作成されることです。
これは、その列のメジャーと構文です。 関数だけを使用するほど単純ではありません。
テーブルを見ると、Converted Sales No Missing 列が表示されます。「欠落なしの通貨レート」列を使用して換算された売上が表示されます。売上が換算されたもう 1 つの列は、DAX で作成された列を使用します。
換算された売上を取得するために使用される関数はです。その列のメジャーを見ると、SUMX が DimDate テーブルを調べていることがわかります。
DAX メソッドと Power Query メソッドの比較
LuckyTemplates では、DAX と Power Query を使用して変換された売上列の両方を実際に比較できます。
これを行うには、変換された sales 列を除くすべての列を削除します。次に、パフォーマンス アナライザーを起動し、[記録の開始] をクリックします。
次に、DAX Studio を開きます。これは、モデル内で物事がどのように、そしてなぜ機能するのかを示すアプリケーションです。その後、マトリックスのクエリをコピーして、スタジオ内に貼り付けます。
比較するには、テスト中の他の列に干渉しないように、最初にそのうちの 1 つをコメントに変換します。
次に、サーバー タイミングとクエリ プランをロードします。パフォーマンスを比較しているため、比較を実行する前にまずキャッシュをクリアしてください。
テストを実行すると、DAX を使用した列で十分なスキャンが行われ、合計時間が 71 ミリ秒になっていることがわかります。
ここで、パワー クエリを使用した他の列を実行します。ただし、その前に、構文内の他の列をコメントに変更します。
この列は 25 ミリ秒間のみ実行され、スキャンは 7 回のみであることがわかります。
DAX メソッドと Power Query メソッドのどちらがより速く、より優れたパフォーマンスを発揮するかが明確にわかります。
結論
LuckyTemplates で Power Query メソッドを使用する利点は、データがすでに保存されていることです。したがって、メジャーはレートを見つけて、簡単な関数を使用してそれを導き出すことができます。
この方法のもう 1 つの点は、計算が複雑になっても速度が低下しないことです。
ただし、これは、要求しているデータをその場で変換する必要がない場合にのみ機能します。その場合は、DAX を使用する必要があります。
ではごきげんよう、
LuckyTemplates でビジュアル ツールチップを作成する
LuckyTemplates ツールチップを使用すると、より多くの情報を 1 つのレポート ページに圧縮できます。効果的な視覚化の手法を学ぶことができます。
Power Automate で HTTP 要求を行う
Power Automate で HTTP 要求を作成し、データを受信する方法を学んでいます。
LuckyTemplates で日付テーブルを作成する方法
LuckyTemplates で簡単に日付テーブルを作成する方法について学びましょう。データの分析と視覚化のための効果的なツールとして活用できます。
2 つの方法による SharePoint 列の検証
SharePoint 列の検証の数式を使用して、ユーザーからの入力を制限および検証する方法を学びます。
SharePoint リストを Excel または CSV ファイルにエクスポート
SharePoint リストを Excel ファイルおよび CSV ファイルにエクスポートする方法を学び、さまざまな状況に最適なエクスポート方法を決定できるようにします。
Power Automate のオンプレミス データ ゲートウェイ
ユーザーがコンピューターから離れているときに、オンプレミス データ ゲートウェイを使用して Power Automate がデスクトップ アプリケーションにアクセスできるようにする方法を説明します。
DAX 数式での LASTNONBLANK の使用
DAX 数式で LASTNONBLANK 関数を使用して、データ分析の深い洞察を得る方法を学びます。
CROSSJOIN 関数の使用方法 – LuckyTemplates および DAX チュートリアル
LuckyTemplates で予算分析とレポートを実行しながら、CROSSJOIN 関数を使用して 2 つのデータ テーブルをバインドする方法を学びます。
TREATAS 関数を使用して LuckyTemplates で仮想リレーションシップを作成する
このチュートリアルでは、LuckyTemplates TREATAS を使用して数式内に仮想リレーションシップを作成する方法を説明します。