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

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,