Life is Like a Boat

忘備録や経済、投資、プログラミングに関するメモやtipsなど

Power QueryをPower Automateから起動させる方法

Power QueryはローコードなETLツールで、プライベートなプロジェクトでは今までPandasでやっていたETL処理をPower Queryにするケースが多いです。 ただPower Queryは通常の単体での使用だとデータ取得の更新間隔や時間指定が必要で、例えばあるファイルが生成されたことをトリガーにしたPower QueryでのETL処理といったことができません。しかし、Power Automateと組みわせるとそれができることがわかりました。

スクリーンショットのように、コネクタの「Refresh a dataflow」(アクション)と「When a dataflow refresh completes」(トリガー)を組み合わせることで実現できます。

後者は実はトリガー、という点がミソです。Dataflowのステータスと終了時刻を取得できるので、TeamsやOutlookへの通知も便利ですね。

PyCon APAC 2023 report 1

Fighting Money Laundering with Python and Open Source Software

I participated in Python APAC 2023, which was held in Tokyo. There were many interesting talks given by "Pythonistas" during the two-day conference. Having previously worked in the banking industry as a Java/Python programmer, I was particularly interested in attending this talk.

The talk explored how Python and Open Source software are tools in fighting money laundering.

For instance, when law enforcement agencies investigate fraud or money laundering, they need to search for a linkage between multiple bank accounts. The process is often time consuming. Deshpande explained that the approach for someone with Python and data science background would take to solve the problem is:

  1. The dataset of accounts are provided from the law enforcement

  2. Generate graph showing links between accounts and transactions

  3. Apply Machine Learning on the graph

  4. Predict possible money laundering case

  5. Generate a report

According to Deshpande, there are two important aspects in providing solutions: Daubert Standard and Benford's Law. Let's delve into these points.

The first is the standard which is used in a court to decide if scientific evidence is good enough to be used during a trial. To do that, from what I understand from his talk, program and algorithm has to be explained in plain language, not code, and the error ratio has to be calculated and validated independently.

The second point he touched upon was the Benford's Law. This is a term I remember I came across when I took a Statistics class back in my college days which was been buried deep in my memory. One would expect that the first number of figures such as city populations and baseball statistics would be anything from 1 to 9, and they would all show up about equally. The twist here is that according to Benford's Law, that's not how it works. In many real life cases, the number "1" is the most common first digits and it accounts 30% of the cases. Here is the link explaining more on that.

(image from https://medium.com/thealexfreeman/benfords-law-9b93f21f4c40)

During his talk, he showed how the Law is applied in investigating money laundering and showed a demo with the following sample case

  1. load a sample dataset containing transaction amounts, bank balances and so on.

  2. load it into Pandas dataframe

  3. get the first digit of transaction amount

  4. plot that as actual distribution and compare it against Benford's Law.

Here is the screenshot from his talk showing the anomaly is detected. You can see the red point indicating the Benford's distribution whereas the bar indicates empirical distribution which suggests anomaly.

Luckily, in Python, there is a library for Benford's Law! pypi.org

It is always interesting to know how people like Deshpande approach the real world problem using Python and scientific theories.

PyCon APAC 2023 Day 1 #pyconapac_3 - YouTube

Benford’s Law (Python). What is Benford’s Law? | by Alex Freeman | thealexfreeman | Medium

DAX, M, PowerFxについて整理する

Power PlatformはMicrosoftの製品で、ビジネスプロセスを自動化し、ビジネスの生産性を向上させるためのプラットフォーム(SaaS)です。

だいたい、入門レベルの解説にも載っていることなのですが、このプラットフォームには、以下の製品が含まれています。

  • Power Apps - ビジネスアプリを開発するためのツール
  • Power Automate - ワークフローやタスクの自動化を行うためのツール
  • Power BI - ビジネスデータの分析と可視化を行うためのツール
  • Power Virtual Agents - AIチャットボットを構築するためのツール

Microsoft社のPower Platformに関する営業資料をみると、ローコード・ノーコードという謳い文句付きで必ず解説されるのですが、プログラミングと全く無縁というわけではなく、生産性を上げる意味でも知っておいて決して損することはないです。このプラットフォームで登場するプログラミング言語は以下の通りです。

  • DAX
  • M
  • PowerFx

それぞれ自分のなかでの整理の意味でまとめてみました。

M

M(Mashup)は、F言語で書かれた関数型言語です。Power BIのPower Queryフレームワークで登場します。Power Queryは軽量ETLエンジンとして機能していて、例えば、列の名前変更、型の変換、ランキングなどの比較的軽い計算操作が行えます。Power QueryはUIからぽちぽち操作して、裏でコードを生成しているので、実際にコード(クエリに近いです)を書くシーンは少ないです。

以下のコードはOrdersテーブルのItem列の値の各単語の最初の文字だけを大文字にして残りの文字を小文字にします。

= Table.TransformColumns(Orders, {"Item", Text.Proper})

Quick tour - PowerQuery M | Microsoft Learn

DAX

DAX(Data Analysis Expression)はPower BIのフロントエンド言語として考えるといいと思います。キャッシュまたはインメモリの保存の概念に近い式エンジンを使用してデータを保持します。複雑なカスタム計算や単純な計算を作成するために使用されます。

以下のコードはTotal Salesというメジャーからチャネル名がStoreの売上を抽出するコードです

Store Sales = CALCULATE([Total Sales], Channel[ChannelName] = "Store")

メジャーは「集計結果を算出するために繰り返し利用できる計算の定義」と覚えておけばいい認識です。

Dax in Power BI - An Guide to DAX Function, Query with Examples

Power Fx

厳密な型付け、宣言型で関数型プログラミング言語です。Excel関数のように使えるので、Microsoftとしては、ビジネスユーザーとプロの開発者の両方がロジックを表現し、問題を解決するための共通の言語という位置付けにしたいようです。Power Apps, Power Automate、Power Virtual Agentで使うシーンがあります。

以下はContact Nameという属性の値をスペース区切りで分割するコードです。"織田 信長"が"織田"と"信長"のリストになります。

Split(ThisItem.'Contact Name', " "))

Transform examples to Power Fx formulas - Power Apps | Microsoft Learn

What is Microsoft Power Fx? An easy 10 minute Guide - PowerPlatformLearn | Rory Neary

pnp.github.io

Dataverse Web APIで外部キーを使ってレコードをUpsertする方法

SalesforceとPower Platformの二刀流で今年度は仕事しています。Salesforceの方は都心方面の事業会社で、Power Platformは埼玉の事業会社なので通勤経路が全く逆方向です。

埼玉の事業会社の方では、Power PlatformをERP的に使う方向になっています。そのため、他システム、特に会計周りのシステムから定期的にデータを抜くOne wayのデータ連携の動線を用意する必要が出てきました。Power PlatformにはDataflowsというクラウド型のETLツールがあり、市場にもノンコードのデータ連携アプリが多々存在します。しかし、1つのレコードから条件に応じて複数のレコードを作成するケースや予算的な制約もあることから

  • Serverless functionであるAzure Functions
  • ETLパイプラインとしてのPandas
  • Azure Funcの世界とPower Platformの世界を繋ぐDataverse Web API
  • ある程度の手作業(会計システムからのデータエクスポート)が柔軟性として残せる

を組み合わせて動線を用意することにしました。

Dataverse Web APIがここで肝になるわけですが、データ連携で外部キーを使ってUpsertするのは鉄板にも関わらず、Dataverse Web APIの解説には、

An upsert operation is exactly like an update. It uses a PATCH request and uses a URI to reference a specific entity. The difference is that if the entity doesn’t exist it will be created. If it already exists, it will be updated. Normally when creating a new entity you will let the system assign a unique identifier. This is a best practice. But if you need to create a record with a specific id value, an upsert operation provides a way to do this. This can be valuable in situation where you are synchronizing data in different systems.

Sometimes there are situations where you want to perform an upsert, but you want to prevent one of the potential default actions: either create or update. You can accomplish this through the addition of If-Match or If-None-Match headers. For more information, see Limit upsert operations.

docs.microsoft.com

としかなく、一番解決策に近いと思われるこちらのブログ記事でもレコードのGUIDでのUpsertを解説しているだけです。

bengribaudo.com

明確に外部キー項目がある場合はどうすればいいのか調べたところ、

{Dataverse Web API}/new_car(new_model_id='abc-1234')

外部キーをシングルクォーテーションで囲ってPATCHリクエストすればいいだけでした。ここではnew_model_idを外部キーとしています。 外部キー項目が整数の場合、シングルクォーテーションなしでもUpsertできるのですが、シングルクォーテーションがない場合、Bad Request - Error in query syntax. の400エラーとなり、これがはまりポイントです。

powerusers.microsoft.com

Dataverseのテーブルレコードが変更された時、変更前と変更後の値が欲しい

例えば、商談テーブルのステータスが何かから「商談成立」に変更になった時、Teamsの全体チャネルに対して「おめでとうございます!」的なお祝いメッセージを送りたい、とします。

Power AppsのCommunityサイトではテーブルのステータス項目とは別に、前の値を保持するための項目を作り、変更されるたびにPower Automateから二つの項目を見て、新しい値で保持用項目を更新というなんとも面倒臭い解決策が提案されていたのですが、この方法を採用すると変更を追いたい項目が2つや3つ増えた時にかなり不便です。

最近、Audit logを見る機会があり、Dataverse Web APIのRetrieveAuditDetails関数を使って前後の値を取得する方法があることを知ったのでその方法をシェアします。

まず、前提としてテーブルのAuditがONになっている必要があります(メッセージにもあるように組織全体のAuditもONになっている必要があります)

次に、Dataverse Web APIを使ってある特定のレコードのAudit Idを取得します。curlコマンドを使っていますが、Power AutomateのHTTPアクションを使って同じことができます。

curl "{Your Org URI}/api/data/v9.2/audits?$filter=_objectid_value%20eq%20{object_id}" \
     -H 'Authorization: Bearer ***** Hidden credentials *****' \
     -H 'Accept: application/json' \
     -H 'Content-Type: application/json; charset=utf-8' \
     -H 'Odata-Version: 4.0' \
     -H 'OData-MaxVersion: 4.0' 

filter=_objectid_value eq {object_id}

でOData filterを使ってObject Idを指定する必要があります。 レスポンスにAudit Idが含まれています。変更履歴が複数ある場合(通常複数あると思います)、最も最新のAudit Idを取得するようにします。

さらに、肝心の変更内容を取得するにはRetrieveAuditDetails関数を使ってこのように取得します。

curl "{Your Org URI}/api/data/v9.2/audits({audit_id})/Microsoft.Dynamics.CRM.RetrieveAuditDetails()" \
     -H 'Authorization: Bearer ***** Hidden credentials *****' \
     -H 'Accept: application/json' \
     -H 'Content-Type: application/json; charset=utf-8' \
     -H 'Odata-Version: 4.0' \
     -H 'OData-MaxVersion: 4.0'

NewValueとOldValueをキーにしたJSONのレスポンスが来るはずです。(スクリーンショットでは変更前はブルボン、変更後がカルビーとなっています。お菓子のことを考えていたからですw)

サンプルで使ったレコードは商談テーブルではないのですが、このやり方で「NewValueが『商談成立』の場合のみに」という条件を作ることができました。

PythonからMicrosoft Dataverseに直接アクセスする方法

MicrosoftのPower PlatformにはPower BIというBIツールがあります。解説本やチュートリアルも豊富に出ていて、株クラのPowerBIマスターの方が以前に株価データ配信のKabuPlusと組み合わせたPowerBIダッシュボードを作成されていて(https://twitter.com/yustock)、アイデア次第でなんでも表現できるな〜と思った次第です。

ただ、すでにPythonを使った分析基盤がある場合にわざわざPower BIの使い方を学んでPython側で溜まった知見の移行をする。。。というのは手が出づらいものです。

それぞれに良さがあり、活用シーンが異なります。すでにPandas+Jupyter資産があり、アドホックな分析をやりたい場合には慣れている方を使った方が良いでしょう。

ここでは、Power PlatformのDataverseに顧客データや営業データが既に溜まっていて、Pandas+Jupyter Notebookを使ってデータ分析するケースを考えたいと思います。

方法その1

一番手軽なやり方は、Power Automateの"When a HTTP request is received"トリガーを使うことです。

数ステップを組み合わせたこちらのPower Automate フローで取引先テーブルを全部返すGETメソッドのエンドポイントができてしまいます。一度のリクエストで最大5000行という制限がありますが、@odata.nextLinkで次の5000件が取れそうな感じなので(検証してません)、回避する方法はググれば探せると思います。

エンドポイントにアクセスしてレスポンスをDataframeにすることができます。 f:id:nerimplo:20211110115209p:plain

方法その2

方法2はOdataプロトコルとRestful APIを使ってDataverseのレコードを取得する方法です。

スクショ付きで説明したいと思います。

まず、Azure Active DirectoryでAppを登録します。ここで言うAppはOAuth認証を使用してDataverse環境に接続できるアプリケーションです。 f:id:nerimplo:20211115162429p:plain

Appによろしく名前をつけて、Single Tenantを選び次へ。 f:id:nerimplo:20211115162507p:plain

client secretを作ります。この値は後で使うので必ずコピーします。値が表示されるのは作成時のみなので、ここでコピーしないとまた作り直す必要があります。 f:id:nerimplo:20211115162602p:plain

Power Platform admin centerに移動します。設定からアプリケーションユーザーを作成します。 f:id:nerimplo:20211115162648p:plain

Appユーザの追加ボタンを押し、右パネルから先程Azure ADで作ったApp名を選び追加します。 f:id:nerimplo:20211115162831p:plain

この後、セキュリティロールをアサインする必要があるのですが、App用に個別のロールを作り、適切な権限を与えることを強く推奨します。特にデータ分析だけに使うのであればRead権限だけで十分なはずです。

ここまでで下準備は完了です。

あとはアクセストークンを取得して、取引先レコードを取得するためのDataverse Web APIのエンドポイントに対してリクエストするだけです。以下サンプルコードです。

import requests
import pandas as pd

LOGIN_URL = 'https://login.microsoftonline.com/{0}/oauth2/token'
TENANT_ID = 'YOUR_TENANT_ID'
APP_ID = 'YOUR_APP_ID'
CLIENT_SECRET = 'YOUR_CLIENT_SECRET'
GRANT_TYPE = 'client_credentials'
RESOURCE = 'https://YOUR_ENV.crm7.dynamics.com/'
DATAVERSE_DATA_URL = '{0}/api/data/v9.1/'.format(RESOURCE)

response = requests.post(LOGIN_URL.format(TENANT_ID), data = {
    'tenant_id': TENANT_ID,
    'client_id': APP_ID,
    'client_secret': CLIENT_SECRET,
    'grant_type': GRANT_TYPE,
    'resource': RESOURCE
})

access_token = response.json().get('access_token')
token_type = response.json().get('token_type')

ACCOUNTS_DATA_URL = f'{DATAVERSE_DATA_URL}accounts'
accounts = requests.get(ACCOUNTS_DATA_URL, headers = {'Authorization': f'{token_type} {access_token}'})

df = pd.DataFrame(accounts.json().get('value'))

解説するポイントとしては、取引先レコードを取得するエンドポイントACCOUNTS_DATA_URLの最後が/accountsと複数形になっている点くらいです。これ結構ハマりポイントだと思いますw https://xxxxx.dynamics.com/api/data/v9.1/accounts

レスポンスに含まれるDataverseレコードはJSON形式になっていますので、これをPandasのDataframeにしてあげればあとはこっちのものです。

さらにOdataプロトコルを使うことでアドホックなクエリを作ることができます。

/accounts?$filter=Name eq 'ABC CORP'

とすると取引先名がABC CORPという会社のレコードを取得することができます。

ちなみにSQL脳の人がODataプロトコルのクエリを作る際にこのチートシートがめちゃくちゃ役立ちますw ぜひ使ってみてください。

skyvia.com

西側諸国が今豊かなのは中世の教会がいとこ同士の結婚を禁じたから

投資理論家であり歴史研究家や神経内科医でもあるウィリアム・バーンスタイン氏がBloomberg RadioのPodcast「Master in Business」で話していたのですが、氏が最近読んで感銘を受けた本として"The Weirdest People in the World"をあげています。

この本でいうweirdは「変な」を意味する形容詞ではなく、Western Educated Industrialized, Rich and Democraticの頭字語だそうです。同書はJoseph Henrich氏という文化人類学者によって書かれ、彼の論題は、西側諸国がなぜ今豊かなのか、その理由として中世の教会がいとこ同士の結婚を禁じたからだ、というのです。

なぜある国は裕福で、ある国は貧乏なのかというのは経済学の長く続く議論の一つですが、これには結構Freaknomics的な驚きを感じます。

教会はいとこ婚だけでなく場合によってはひいひいひいひい爺ちゃんが共通の人(Fifth cousin)との結婚も禁じたとのこと。ここまでくると事実上、生まれ育った町を離れ、結婚相手を探さなければなりません。

数世紀にわたるこの制度は欧州社会の親族関係の構造を根本的に変えていきます。伝統的な親族関係では年長者に従う道徳的価値がありました。しかし、教会が親族以外との結婚を定めたことで、個人主義、不適合、仲間内への偏見のなさなどの新しい価値観が生まれたと言います。このPodcastバーンスタイン氏は“radius of trust”と表現していましたが、中世の教会がいとこ同士の結婚を禁じたことで、この半径が大きくなった社会、つまり見知らぬ人を信頼するようになった社会が豊かな現代社会の性質だといいます。経済的にも政治的にも制度的にも劣っていて、信頼の半径が非常に小さい社会はうまくいっていない傾向があります。例えば、北イタリアと南イタリアでは前者は信頼半径は非常に高く、後者は非常に低くなっています。

「trust」の崩壊が幾たびもの金融危機を引き起こしました。一方で「trust」は不確実性や取引コストを削減し、交換の効率を高め、専門化を促進し、アイデアや人的・物的資本への投資を促す役割があるでしょう。その広がりは中世の教会によるルールが要因だったという下りに、文化人類学的な視点で世の中を見る面白さを感じました。

Transcriptはこちらです。この「Master in Business」は、投資や経営だけでなく経済学、社会学、危機管理など様々な専門家が話すPodcastなので、視野や視点を広げる意味でもかなりおすすめです。 ritholtz.com