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

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

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

現状確認

Power BI Desktop では CDS コネクターがあり、そちらを使うと以下のようにデータが取れます。

このコネクターの優れているところは、オプションセットの値は、別途 _display とついた列に、対応する表示名が出るところです。

解決策検討

まず Common Data Service は API でエンティティのメタデータから列名を取得することができます。
参照Querying EntityMetadata attributes
つまりこの結果と CDS のクエリ結果をうまく使えば表示名を使ったデータセットが作れるはずです。

表示名取得

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

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 のクエリエディターでごにょごにょすれば、きっと同じ結果にたどり着きます。

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

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

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

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 と付与

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 で列名をリネーム

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

結果は意図したとおりに表示されました。

日本語環境でもテスト

同じクエリで結果が表示されました。

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

入れ子

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

変数のスコープ

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

戻りの型

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

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

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
Expressions, values, and let expression