Join with a Text String as Key instead of a Column Reference

[JeFri]

Hello Everyone!

I am working on a Power BI report based on D365FO using the new -BIENTITY Entities. However, they often have the technical ENUM Values instead of their respective ENUM Labels in their fields. I now need to perform a number of JOIN actions to transform them into readable values.

I am not sure how to pass a text string as a key without Power Query looking for a column reference. Writing this as a SQL statement it is easy, but I have not figured out how to do this in Power Query yet, so I thought I would just ask this here.

As an example, I'll use the Purchase Order Headers entity "PURCHTABLEBIENTITY" and the ENUM table "SRSANALYSISENUMS" to look for the PURCHASETYPE value label.

let
    get_PurchHeader =
        Sql.Database( SQL_Server, SQL_Database )
        { [ Schema = "dbo", Item = "PURCHTABLEBIENTITY" ] }[Data],
    
    PurchHeader_Select =
        Table.SelectColumns(
            get_PurchHeader,
            {
                "DATAAREAID",
                "RECID",
                "PURCHID",
                "PURCHSTATUS",
                "PURCHNAME",
                "ORDERACCOUNT",
                "INVOICEACCOUNT",
                "PURCHASETYPE"
            }

    get_ENUMs =
        Sql.Database( SQL_Server, SQL_Database )
        { [ Schema = "dbo", Item = "SRSANALYSISENUMS" ] }[Data],

    ENUMs_Select =
        Table.SelectColumns(
            get_ENUMs,
            {
                "ENUMITEMLABEL",
                "ENUMITEMNAME",
                "ENUMITEMVALUE",
                "ENUMNAME",
                "LANGUAGEID",
                "RECID"
            }
        )
...

I now need to JOIN these two tables with the following Keys:

• column [ENUMITEMVALUE] = column [PURCHASETYPE]
• column [ENUMNAME] = string "PurchaseType"

(Note: In the screenshot above, I have already renamed the columns. I will ignore this in the code examples though.)

I have tried putting them in as you would usually do in SQL, as "PurchaseType" and "en-us", but it keeps on looking for a column of that same name.

...
    PurchHeader_Merge_PurchaseType =
        Table.NestedJoin(
            PurchHeader_Select { "PURCHASETYPE", "PurchaseType" },
            ENUMs_Select, { "ENUMITEMVALUE", "ENUMNAME" },
            "PurchaseType_Enum",
            JoinKind.LeftOuter
        ),

    PurchHeader_Expand_PurchaseType =
        Table.ExpandTableColumn(
            PurchHeader_Merge_PurchaseType,
            "PurchaseType_Enum",
            {
                "ENUMITEMLABEL"
            },
            {
                "PurchaseType_Value"
            }
        )
...

A suggestion I found was by using Table.AddColumn() to add "PurchaseType" as referenceable column, but that seems to me to not be the best way to do it. Since I will have to repeat this with a number of other columns (i.e. PURCHSTATUS), sometimes with an additional "LANGUAGEID" string ("en-us"), I would not want to use this as a shortcut but do it properly.

How would I put theis in as fixed text? Can anyone help me solve this?

Thank you for your help!

JeFri

[DataNinja777 · Accepted solution]

Hi @JeFri ,

To join the tables while using a fixed text string as a key in Power Query (M), you need to ensure that ENUMNAME is filtered to "PurchaseType" before performing the merge. Since Power Query expects column references for joins, directly using a string like "PurchaseType" inside the Table.NestedJoin function does not work. The proper approach is to pre-filter the ENUM table before the join operation.

The solution involves first loading both the PURCHTABLEBIENTITY and SRSANALYSISENUMS tables. After selecting the necessary columns, the ENUMS table is filtered using Table.SelectRows to include only rows where ENUMNAME is "PurchaseType". This filtering step ensures that only relevant ENUM values are considered in the subsequent join operation.

let
    get_PurchHeader = Sql.Database(SQL_Server, SQL_Database){ [ Schema = "dbo", Item = "PURCHTABLEBIENTITY" ] }[Data],

    PurchHeader_Select = Table.SelectColumns(get_PurchHeader, 
        {"DATAAREAID", "RECID", "PURCHID", "PURCHSTATUS", "PURCHNAME", "ORDERACCOUNT", "INVOICEACCOUNT", "PURCHASETYPE"}),

    get_ENUMs = Sql.Database(SQL_Server, SQL_Database){ [ Schema = "dbo", Item = "SRSANALYSISENUMS" ] }[Data],

    ENUMs_Select = Table.SelectColumns(get_ENUMs, 
        {"ENUMITEMLABEL", "ENUMITEMNAME", "ENUMITEMVALUE", "ENUMNAME", "LANGUAGEID", "RECID"}),

    ENUMs_Filtered = Table.SelectRows(ENUMs_Select, each [ENUMNAME] = "PurchaseType"),

    PurchHeader_Merge_PurchaseType = Table.NestedJoin(
        PurchHeader_Select, 
        {"PURCHASETYPE"}, 
        ENUMs_Filtered, 
        {"ENUMITEMVALUE"}, 
        "PurchaseType_Enum", 
        JoinKind.LeftOuter
    ),

    PurchHeader_Expand_PurchaseType = Table.ExpandTableColumn(
        PurchHeader_Merge_PurchaseType, 
        "PurchaseType_Enum", 
        {"ENUMITEMLABEL"}, 
        {"PurchaseType_Value"}
    )

in
    PurchHeader_Expand_PurchaseType

By applying the filter first, Power Query does not mistake "PurchaseType" for a column reference. The join then correctly matches PURCHASETYPE from PURCHTABLEBIENTITY with ENUMITEMVALUE from SRSANALYSISENUMS, returning the corresponding ENUMITEMLABEL. This approach eliminates the need for workarounds such as adding a new column with the string "PurchaseType" for the join, making the transformation cleaner and more scalable.

Best regards,

[JeFri]

Examining the solution, I have a followup-question to this: This step seems to break Query Folding, I cannot view the Native Query resulting from this. Do you know whether Query Folding will still work as expected even though I cannot see the Native Query after this, or should I then just by default put this as the last steps and let Power BI handle this?

[JeFri]

That works and makes a lot of sense now that you say that. Thank you so much!