Excel Power Query问题：SSN字段错误及筛选后新增列丢失

问题原因分析 #

1. SSN字段找不到错误：

   • 部分行的Raw Data长度不足，导致Text.Middle([Raw Data],1,9)无法生成有效SSN值，筛选时引用空值/无效值触发错误；
   • 也可能是PA_SSN_LIST中的SSN格式与生成的SSN列不匹配（比如列表带连字符，而生成的是纯数字文本）。

2. 筛选后列丢失：
   原脚本中筛选步骤#"Filtered Rows"的数据源是#"AddedSSN"——这是仅添加了Record Type、SSN和Raw Data的中间表，后续新增的所有列未被纳入筛选操作的数据源，因此会丢失。

修正后的脚本 #

let
    Buffered.List = List.Buffer(PA_SSN_LIST),
    Source = Csv.Document(File.Contents("H:\Analysis\State Data Match\State Programs\WI_StateDataMatch.TXT"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Raw Data"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column2"}),
    AddedRecordType = Table.AddColumn(#"Removed Columns", "Record Type", each Text.Start([Raw Data],1)),
    AddedSSN = Table.AddColumn(AddedRecordType, "SSN", each 
        // 增加长度判断，避免因Raw Data过短生成无效值
        if Text.Length([Raw Data]) >= 10 then Text.Middle([Raw Data],1,9) else null
    ),
    AddedStatePassBack = Table.AddColumn(AddedSSN, "State Pass Back", each Text.Middle([Raw Data],10,10)),
    AddedFirstName = Table.AddColumn(AddedStatePassBack, "First Name", each Text.Middle([Raw Data],40,16)),
    AddedLastName = Table.AddColumn(AddedFirstName, "Last Name", each Text.Middle([Raw Data],20,20)),
    AddedCasePassBack = Table.AddColumn(AddedLastName, "Case Pass Back", each Text.Middle([Raw Data],56,15)),
    AddedFIPSCode = Table.AddColumn(AddedCasePassBack, "FIPS Code", each Text.Middle([Raw Data],71,5)),
    AddedAdditionalPassBack = Table.AddColumn(AddedFIPSCode, "Additional Pass Back", each Text.Middle([Raw Data],76,23)),
    // 修改筛选数据源为最后一步添加完所有列的表
    #"Filtered Rows" = Table.SelectRows(#"AddedAdditionalPassBack" , each 
        // 增加非空判断，避免引用null值触发错误
        [SSN] <> null and List.Contains(Buffered.List,[SSN])
    )
in
    #"Filtered Rows"

关键修复点 #

• 列丢失修复：将筛选步骤的数据源从#"AddedSSN"改为#"AddedAdditionalPassBack"，确保筛选在所有列添加完成后执行，保留全部新增列。
• SSN字段错误修复：
  1. 生成SSN列时增加长度判断，避免因Raw Data过短生成null或无效值；
  2. 筛选时增加[SSN] <> null判断，防止引用空值触发字段找不到的错误；
  3. 确保PA_SSN_LIST中的SSN为9位纯数字文本，与生成的SSN列格式完全一致（不要带-或空格）。

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