Common Data Service の表示名を Power BI で使う : M 言語でクエリ

先日コミュニティで、「Power BI からCDS のテーブルに接続した際は、英字のフィールド名になります」という話があがり、現状列名を手動で変更するという回答が出ていました。確かに仕様上そんな感じだった気がしたのですが、面倒だから何か回避策がないか考えてみました。

尚、今回の手順は CDS で表示名を使うための正しい手順を示したものではなく、同じような状況で何ができるかを検討した結果です。

# 現状確認

Power BI Desktop では CDS コネクターがあり、そちらを使うと以下のようにデータが取れます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/50a06241-b1b4-6687-9c0f-e6026552ea4f.png)
このコネクターの優れているところは、オプションセットの値は、別途 _display とついた列に、対応する表示名が出るところです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/59e50412-d2f5-2be3-3f21-a9d4c685a05e.png)

# 解決策検討

まず Common Data Service は API でエンティティのメタデータから列名を取得することができます。
[参照Querying EntityMetadata attributes](https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-metadata-web-api#querying-entitymetadata-attributes)
つまりこの結果と CDS のクエリ結果をうまく使えば表示名を使ったデータセットが作れるはずです。

# 表示名取得

まずは表示名を取得します。ドキュメントを参考に Power BI の Web コネクターを利用してデータを取得しました。認証は組織認証です。

```fsharp
let 
    BaseUrl = "https://orgcc4a5456.crm7.dynamics.com/",
    EntityDefinitions = Json.Document(Web.Contents(Text.Combine({
        BaseUrl,
        "api/data/v9.0/EntityDefinitions(LogicalName='account')?$select=DisplayName&$expand=Attributes($select=LogicalName,DisplayName)"}))),
    Attributes = EntityDefinitions[Attributes],
    ConvertedToTable = Table.FromList(Attributes, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"LogicalName", "DisplayName"}, {"LogicalName", "DisplayName"}),
    #"Expanded DisplayName" = Table.ExpandRecordColumn(#"Expanded Column1", "DisplayName", {"LocalizedLabels"}, {"LocalizedLabels"}),
    #"Expanded LocalizedLabels" = Table.ExpandListColumn(#"Expanded DisplayName", "LocalizedLabels"),
    #"Expanded LocalizedLabels1" = Table.ExpandRecordColumn(#"Expanded LocalizedLabels", "LocalizedLabels", {"Label"}, {"Label"}),
    LocalizedLabels = Table.SelectRows(#"Expanded LocalizedLabels1", each ([Label] <> null))
in
    LocalizedLabels
```
CDS は多言語に対応していることもありデータ構造が少し深いですが、サンプルを見ながら Power BI Desktop のクエリエディターでごにょごにょすれば、きっと同じ結果にたどり着きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/0993eb78-c9e8-a442-1620-810b02aa0608.png)

# 表示名を使ったテーブル作成

やり方はいくつもありますが、今回はまずデータとラベル取得用の関数を定義しました。

- GetAttributesLabel: 上記クエリを関数化しただけ
- GetCDSData: CDS コネクターを使ってデータ取得したクエリを再利用

```fsharp
let
    BaseUrl = "https://orgcc4a5456.crm7.dynamics.com/",
    GetAttributesLabel = () =>
        let 
            EntityDefinitions = Json.Document(Web.Contents(Text.Combine({
                BaseUrl,
                "api/data/v9.0/EntityDefinitions(LogicalName='account')?$select=DisplayName&$expand=Attributes($select=LogicalName,DisplayName)"}))),
            Attributes = EntityDefinitions[Attributes],
            ConvertedToTable = Table.FromList(Attributes, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"LogicalName", "DisplayName"}, {"LogicalName", "DisplayName"}),
            #"Expanded DisplayName" = Table.ExpandRecordColumn(#"Expanded Column1", "DisplayName", {"LocalizedLabels"}, {"LocalizedLabels"}),
            #"Expanded LocalizedLabels" = Table.ExpandListColumn(#"Expanded DisplayName", "LocalizedLabels"),
            #"Expanded LocalizedLabels1" = Table.ExpandRecordColumn(#"Expanded LocalizedLabels", "LocalizedLabels", {"Label"}, {"Label"}),
            LocalizedLabels = Table.SelectRows(#"Expanded LocalizedLabels1", each ([Label] <> null))
        in
            LocalizedLabels,
    GetCDSData = () =>
        let
            DataSoure = Cds.Entities(BaseUrl, null),
            entities = DataSoure{[Group="entities"]}[Data],
            accounts = entities{[EntitySetName="accounts"]}[Data]
        in
            accounts,
    Labels = GetAttributesLabel(),
    Data = GetCDSData()
in Data 
```
上記クエリでは GetCDSData の結果を表示しているので、まだこのままでは列は論理名です。

次に論理名から表示名を取得する関数を追加します。

- オプションセットなどは _display が論理名に追加されるので、まずそれをチェック
- Table.FindText により論理名を含んだ列を取得
- Table.Column により Label 列のみ取得
- List.First により一番初めの結果だけを取得
- 最後に _display がついていたものは Display と付与

```fsharp
FindLabel = (logicalName as text, labels as table)=>
    let             
        LogicalNameTemp = if Text.Contains(logicalName, "_display") then Text.Replace(logicalName, "_display", "") else logicalName,
        FindResult = List.First(Table.Column(Table.FindText(labels, LogicalNameTemp), "Label"), logicalName),
        Result = if Text.Contains(logicalName, "_display") then Text.Combine({FindResult," Display"}) else FindResult          
    in Result,
```
全てを組み合わせたものを利用して結果を取得。

- Table.TransformColumnNames で列名をリネーム

```fsharp
let
    BaseUrl = "https://orgffbd4006.crm7.dynamics.com/",
    GetAttributesLabel = () =>
        let 
            EntityDefinitions = Json.Document(Web.Contents(Text.Combine({
                BaseUrl,
                "api/data/v9.0/EntityDefinitions(LogicalName='account')?$select=DisplayName&$expand=Attributes($select=LogicalName,DisplayName)"}))),
            Attributes = EntityDefinitions[Attributes],
            ConvertedToTable = Table.FromList(Attributes, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"LogicalName", "DisplayName"}, {"LogicalName", "DisplayName"}),
            #"Expanded DisplayName" = Table.ExpandRecordColumn(#"Expanded Column1", "DisplayName", {"LocalizedLabels"}, {"LocalizedLabels"}),
            #"Expanded LocalizedLabels" = Table.ExpandListColumn(#"Expanded DisplayName", "LocalizedLabels"),
            #"Expanded LocalizedLabels1" = Table.ExpandRecordColumn(#"Expanded LocalizedLabels", "LocalizedLabels", {"Label"}, {"Label"}),
            LocalizedLabels = Table.SelectRows(#"Expanded LocalizedLabels1", each ([Label] <> null))
        in
            LocalizedLabels,
    GetCDSData = () =>
        let
            DataSoure = Cds.Entities(BaseUrl, null),
            entities = DataSoure{[Group="entities"]}[Data],
            accounts = entities{[EntitySetName="accounts"]}[Data]
        in
            accounts,
    FindLabel = (logicalName as text, labels as table)=>
        let             
            LogicalNameTemp = if Text.Contains(logicalName, "_display") then Text.Replace(logicalName, "_display", "") else logicalName,
            FindResult = List.First(Table.Column(Table.FindText(labels, LogicalNameTemp), "Label"), logicalName),
            Result = if Text.Contains(logicalName, "_display") then Text.Combine({FindResult," Display"}) else FindResult          
        in Result,
    Labels = GetAttributesLabel(),
    Data = GetCDSData(),
    Results = Table.TransformColumnNames(Data, each FindLabel(_, Labels))
in Results
```
結果は意図したとおりに表示されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/47f6d318-e58d-b763-5b65-e4f71fbbf2a2.png)

# 日本語環境でもテスト

同じクエリで結果が表示されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/82079518-4dc5-bdfd-4c05-55715ec1ab89.png)

# 利用した M 言語関数の考察

## 入れ子

let 内の処理を書き、in で結果を返すのが基本ですが、複数の関数を使う場合に入れ子にすることができます。今回も let 内に別の let を定義しています。

## 変数のスコープ

例えば BaseUrl は GetCDSData の中でも使えますが、GetCDSData 内で指定した変数はその外部では利用できません。もし変数が見つからないというエラーが出た場合は、スコープが正しいか、もしくはタイプミスをしていないかを確認します。

## 戻りの型

M 言語を使うと、データ操作やテーブル操作などほぼすべてのことができます。意識すべき点としては、戻り値の型です。

例えば以下のコードを見ると、3 つの関数が入れ子になっています。

```fsharp
FindResult = List.First(Table.Column(Table.FindText(labels, LogicalNameTemp), "Label"), logicalName)
```
Table.FindText は結果としてテーブル型のデータを返します。コードを書いている側からすると 1 行しか結果を期待していませんが、指定した文字列を含む列を返すため、0 行以上となります。
次に Table.Column は特定の列を返すため、結果はリストとなります。こちらもアイテムが 1 しかなくてもリスト型です。
最後に List.First はリストの一番初めの値を返しますが、戻り型は any です。これは値が文字列か数値か分からないためです。

## 既定値

List.First 関数は既定値を利用できるため、結果が null の場合は logicalName を返すようにしています。optionalValue も有効活用するとコードがシンプルになります。

## 他の方法

結果が同じになる方法は複数あります。今回の場合 Table.RenameColumns も試してみてください。

# まとめ

M 言語を使えると、既定のコネクターができない事も補完できる可能性が高くなります。また最終結果が同じでも様々な方法があるので、大きなデータセットを使った場合にパフォーマンスが出ない場合なども、他の手段がないか探してみてください。

# 参照

[Power Query M function reference](https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference)
[Expressions, values, and let expression](https://docs.microsoft.com/en-us/powerquery-m/expressions-values-and-let-expression)