Power Query中ROE数值无法筛选与合并问题求助

• 数据源为SQL表，ROE列存在如23,85（逗号作为小数分隔符）的数值，直接筛选该值提示不存在，合并功能也无法匹配
• 同一列内部分数值可正常筛选/合并，无明显规律，已完成数据刷新、上传操作，相关场景已截图
• 在Power BI/Excel中直接执行SQL语句无额外处理时不生效，Power Query分组操作后部分数值出现异常

相关M代码 #

let
    Source = Sql.Database("xxxxxxxxxxx", "xxxxxxx"),
    stage_onecore_RoeCalculation = Source{[Schema="stage_onecore",Item="RoeCalculation"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(stage_onecore_RoeCalculation,{"Contract No_", "Entry No_", "ROE _"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Contract No_"}, {{"MinEntryNo", each List.Min([Entry No_]), Int64.Type}, {"All", each _, type table [Contract No_=nullable text, Entry No_=nullable number, ROE _=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Entry No_", "ROE _"}, {"Entry No_", "ROE _"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded All", each ([Entry No_] = [MinEntryNo])),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"ROE _", "ROE %"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Contract No_] = "12187012" or [Contract No_] = "15761011" or [Contract No_] = "1100000064") and ([#"ROE %"] = 23.93))
in
    #"Filtered Rows"

尝试的SQL语句 #

SELECT
  [Contract No_],
  [Entry No_],
  [ROE _]
FROM
  stage_onecore.RoeCalculation
WHERE
  [Entry No_] IN (
    SELECT
      MIN([Entry No_])
    FROM
      stage_onecore.RoeCalculation
    GROUP BY
      [Contract No_]
  )

解决方案 #

1. 修复小数分隔符与数据类型不匹配问题 #

• SQL表中ROE列可能存储为字符串类型，而非数值类型，导致23,85（逗号分隔）无法被识别为数值23.85
• 在Power Query中添加转换步骤：
  1. 选中ROE列，右键选择更改类型→使用区域设置
  2. 选择对应区域（如欧洲地区，小数分隔符为逗号），设置类型为小数
  3. 转换后再尝试筛选或合并操作

2. 规避浮点数精度匹配误差 #

• 浮点数存储存在精度误差，直接用=[ROE %] = 23.93会因精度问题匹配失败，改为范围筛选：

  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Contract No_] = "12187012" or [Contract No_] = "15761011" or [Contract No_] = "1100000064") and ([#"ROE %"] >= 23.929 and [#"ROE %"] <= 23.931))
  

3. 在SQL层面预处理数据 #

• 修改SQL语句，将字符串类型的ROE转换为数值，同时替换小数分隔符：

  SELECT
    [Contract No_],
    [Entry No_],
    CAST(REPLACE([ROE _], ',', '.') AS DECIMAL(18,2)) AS [ROE _]
  FROM
    stage_onecore.RoeCalculation
  WHERE
    [Entry No_] IN (
      SELECT
        MIN([Entry No_])
      FROM
        stage_onecore.RoeCalculation
      GROUP BY
        [Contract No_]
    )
  

  导入Power Query前完成数据类型转换，避免后续匹配问题

4. 验证并强制设置数据类型 #

• 检查Power Query中ROE列的类型标识，若显示为text而非number，手动强制转换为数值类型，避免自动类型识别错误

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