如何在Power BI的Power Query中实现带SUBSTRING的内连接？

核心问题解决：用Power Query实现SQL的SUBSTRING关联 #

SQL中的INNER JOIN protocolos p ON n.codigonotario = SUBSTRING(p.codigoprotocolo, 2, LEN(n.codigonotario))无法直接用Power Query的常规等值连接实现，因为需要动态根据NOTARIOS表中字段长度截取PROTOCOLOS表的字符串。以下是完整的实现步骤：

1. 加载所有需要的表 #

先在Power Query中导入所有涉及的表：

let
    Origen = Sql.Databases("LAPTOP-OSCAR"),
    Far = Origen{[Name="Far"]}[Data],
    // 加载所有关联表
    NOTARIOS = Far{[Schema="dbo",Item="NOTARIOS"]}[Data],
    PROTOCOLOS = Far{[Schema="dbo",Item="PROTOCOLOS"]}[Data],
    CONCEPTOSPROTOCOLOS = Far{[Schema="dbo",Item="CONCEPTOSPROTOCOLOS"]}[Data],
    DATOSCONCEPTOS = Far{[Schema="dbo",Item="DATOSCONCEPTOS"]}[Data],
    CAMPOSDATOSCONCEPTOS = Far{[Schema="dbo",Item="CAMPOSDATOSCONCEPTOS"]}[Data],
    CONCEPTOS = Far{[Schema="dbo",Item="CONCEPTOS"]}[Data],

2. 实现NOTARIOS与PROTOCOLOS的动态子串关联 #

• 先给NOTARIOS添加字段长度列，用于后续截取：

  NOTARIOS_WITH_LENGTH = Table.AddColumn(NOTARIOS, "CODIGO_LENGTH", each Text.Length([CODIGONOTARIO])),
  

• 用自定义筛选实现子串匹配的内连接：

  NOTARIOS_JOIN_PROTOCOLOS = Table.AddColumn(NOTARIOS_WITH_LENGTH, "PROTOCOLOS_MATCH", each 
        Table.SelectRows(PROTOCOLOS, (p) => 
            [CODIGONOTARIO] = Text.Range(p[CODIGOPROTOCOLO], 1, [CODIGO_LENGTH])
        )
    ),
    #"展开PROTOCOLOS_MATCH" = Table.ExpandTableColumn(NOTARIOS_JOIN_PROTOCOLOS, "PROTOCOLOS_MATCH", 
        {"CODIGOPROTOCOLO", "FECHAPROTOCOLO", "MONEDA", "ENCARGADO"}, 
        {"PROTOCOLOS.CODIGOPROTOCOLO", "PROTOCOLOS.FECHAPROTOCOLO", "PROTOCOLOS.MONEDA", "PROTOCOLOS.ENCARGADO"}),
  

  注意：Power Query的Text.Range是0-based索引，SQL的SUBSTRING(str,2,len)对应Text.Range(str,1,len)。

3. 实现其他多条件关联 #

对于DATOSCONCEPTOS与CONCEPTOSPROTOCOLOS的双条件关联（CODIGOPROTOCOLO匹配 + SUBSTRING(path,6,4)相等），先给两个表添加子串计算列，再做等值连接：

// 给CONCEPTOSPROTOCOLOS提取PATH子串
    #"添加CP_PATH_SUB" = Table.AddColumn(#"展开CONCEPTOSPROTOCOLOS", "CP_PATH_SUB", each 
        Text.Range([CONCEPTOSPROTOCOLOS.PATH], 5, 4) // SQL的SUBSTRING(path,6,4)对应0-based的5，长度4
    ),
    // 给DATOSCONCEPTOS提取PATH子串
    DATOSCONCEPTOS_WITH_SUB = Table.AddColumn(DATOSCONCEPTOS, "DC_PATH_SUB", each 
        Text.Range([PATH], 5, 4)
    ),
    // 双条件关联
    #"关联DATOSCONCEPTOS" = Table.NestedJoin(#"添加CP_PATH_SUB", {"PROTOCOLOS.CODIGOPROTOCOLO", "CP_PATH_SUB"}, 
        DATOSCONCEPTOS_WITH_SUB, {"CODIGOPROTOCOLO", "DC_PATH_SUB"}, "DATOSCONCEPTOS", JoinKind.Inner),
    #"展开DATOSCONCEPTOS" = Table.ExpandTableColumn(#"关联DATOSCONCEPTOS", "DATOSCONCEPTOS", 
        {"VALORNUMERICO", "CODIGOCAMPODATOS", "PATH"}, 
        {"DATOSCONCEPTOS.VALORNUMERICO", "DATOSCONCEPTOS.CODIGOCAMPODATOS", "DATOSCONCEPTOS.PATH"}),

4. 应用筛选条件 #

将SQL中的WHERE条件转化为Power Query的Table.SelectRows：

#"筛选FECHAPROTOCOLO" = Table.SelectRows(#"展开CONCEPTOS", each 
        [PROTOCOLOS.FECHAPROTOCOLO] >= #date(2022,1,1) and [PROTOCOLOS.FECHAPROTOCOLO] <= #date(2022,12,31)),
    #"筛选CODIGONOTARIO" = Table.SelectRows(#"筛选FECHAPROTOCOLO", each 
        Text.StartsWith([CODIGONOTARIO], "Prueba") or "Prueba" = ""),
    #"筛选CATEGORIACAMPO" = Table.SelectRows(#"筛选CODIGONOTARIO", each 
        List.Contains({"CUANTIA", "HONORARIOSBRUTO", "HONORARIOSREDUCCION"}, [CAMPOSDATOSCONCEPTOS.CATEGORIACAMPO])),
    #"筛选ENCARGADO" = Table.SelectRows(#"筛选CATEGORIACAMPO", each 
        Text.StartsWith([PROTOCOLOS.ENCARGADO], "Prueba") or "Prueba" = ""),
    #"筛选CODIGOPROTOCOLO" = Table.SelectRows(#"筛选ENCARGADO", each 
        Text.StartsWith([PROTOCOLOS.CODIGOPROTOCOLO], "p") or Text.StartsWith([PROTOCOLOS.CODIGOPROTOCOLO], "c")),
    #"筛选INDICEUNICO" = Table.SelectRows(#"筛选CODIGOPROTOCOLO", each 
        Text.From([CONCEPTOSPROTOCOLOS.INDICEUNICO]) = "1" or 1 = 0),

5. 添加计算列与分组聚合 #

实现SQL中SELECT的派生列和GROUP BY的聚合逻辑：

#"添加CODIGOCONCEPTO" = Table.AddColumn(#"筛选INDICEUNICO", "CODIGOCONCEPTO", each 
        Text.Trim([CONCEPTOSPROTOCOLOS.INDICEUNICO])),
    #"添加DESCRIPCIONCONCEPTO" = Table.AddColumn(#"添加CODIGOCONCEPTO", "DESCRIPCIONCONCEPTO", each 
        [CONCEPTOSPROTOCOLOS.TEXTOCONCEPTO] & " (" & Text.Trim([CONCEPTOSPROTOCOLOS.INDICEUNICO]) & ")"),
    #"添加NUM_OPERACIONES" = Table.AddColumn(#"添加DESCRIPCIONCONCEPTO", "NUM_OPERACIONES", each 
        if Text.Range([DATOSCONCEPTOS.PATH], 10, 4) = "0001" then [CONCEPTOSPROTOCOLOS.INDICEUNICO] else null),
    #"添加NUM_BASES" = Table.AddColumn(#"添加NUM_OPERACIONES", "NUM_BASES", each 
        if Text.Range([DATOSCONCEPTOS.PATH], 10, 4) = "0001" then [CONCEPTOSPROTOCOLOS.BASES] else null),
    #"添加TotalCuantias计算项" = Table.AddColumn(#"添加NUM_BASES", "TotalCuantias_ITEM", each 
        [DATOSCONCEPTOS.VALORNUMERICO] / (if [PROTOCOLOS.MONEDA] = null or [PROTOCOLOS.MONEDA] = 0 then 166.386 else 1)),

    // 分组聚合
    #"分组依据" = Table.Group(#"添加TotalCuantias计算项", 
        {"CODIGONOTARIO", "NOMBRENOTARIO", "CONCEPTOSPROTOCOLOS.INDICEUNICO", "CONCEPTOSPROTOCOLOS.TEXTOCONCEPTO", "DATOSCONCEPTOS.PATH", "CAMPOSDATOSCONCEPTOS.CATEGORIACAMPO"},
        {
            {"NUM_OPERACIONES", each List.Count(List.RemoveNulls([NUM_OPERACIONES])), Int64.Type},
            {"NUM_BASES", each List.Sum(List.RemoveNulls([NUM_BASES])), type number},
            {"TotalCuantias", each List.Sum([TotalCuantias_ITEM]), type number},
            {"CODIGOCONCEPTO", each List.First([CODIGOCONCEPTO]), type text},
            {"DESCRIPCIONCONCEPTO", each List.First([DESCRIPCIONCONCEPTO]), type text}
        }),

    // 调整列顺序
    #"调整列顺序" = Table.ReorderColumns(#"分组依据", 
        {"CODIGONOTARIO", "NOMBRENOTARIO", "NUM_OPERACIONES", "NUM_BASES", "TotalCuantias", "CODIGOCONCEPTO", "DESCRIPCIONCONCEPTO", "CAMPOSDATOSCONCEPTOS.CATEGORIACAMPO"})
in
    #"调整列顺序"

关键注意事项 #

• Text.Range与SQLSUBSTRING的索引差异：SQL是1-based，Power Query是0-based，截取起始位置需要减1。
• 动态长度匹配需通过自定义列筛选实现，无法直接用常规等值连接。
• 聚合逻辑中的CASE判断，先通过计算列标记有效数据，再用List.RemoveNulls过滤后聚合。

内容的提问来源于stack exchange，提问作者Óscar73