Power Query合并时转换Null为Number报错问题求助

• 工具稳定运行1年，近期刷新报错，问题锁定在UPC字段左连接合并步骤
• 双方UPC字段均为Int64.Type，数据匹配正常，但所有列显示错误标识
• 已确认SQL查询数据无Null值，过滤逻辑正常

截图说明 #

• 截图1：刷新时的报错弹窗
• 截图2：合并用SQL数据（已过滤Null，无错误）
• 截图3：合并步骤界面（数据匹配正确，但全列带错误标记）

相关代码 #

主查询（SharePoint数据源） #

let
    Source = SharePoint.Files("REMOVED", [ApiVersion = 15]),
    #"Filtered Rows1" = Table.SelectRows(Source, each ([Folder Path] = "REMOVED")),
    Custom1 = Table.SelectRows(#"Filtered Rows1", let latest = List.Max(#"Filtered Rows1"[Date modified]) in each [Date modified] = latest),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "620 BEER-WINE")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Custom.Data"}),
    #"Expanded Custom.Data1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5", "Custom.Data.Column6", "Custom.Data.Column7", "Custom.Data.Column8", "Custom.Data.Column9", "Custom.Data.Column10", "Custom.Data.Column11", "Custom.Data.Column12", "Custom.Data.Column13", "Custom.Data.Column14"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Custom.Data1", each ([Custom.Data.Column2] <> null) and ([Custom.Data.Column6] <> null) and ([Custom.Data.Column7] <> "VARIES" and [Custom.Data.Column7] <> "VARIOUS")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows2", [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Promoted Headers", {{"UPC/GTIN", type text}}, "en-US"), {{"UPC/GTIN", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "UPC/GTIN"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"UPC/GTIN", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "UPC/GTIN"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter1",{{"UPC/GTIN", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"UPC/GTIN", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Display Section Name", "Priority", "State", "Shelf", "Segmentation", "Retail", "Event Participation", "Distributor", "Size"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"UPC/GTIN"}, vwVIP_CCM_Products, {"UPC_Retail_Trimmed"}, "vwVIP_CCM_Products", JoinKind.LeftOuter),
    #"Expanded vwVIP_CCM_Products" = Table.ExpandTableColumn(#"Merged Queries", "vwVIP_CCM_Products", {"IYSTAT", "ProdID", "Supplier", "Product", "Supplier_Code"}, {"vwVIP_CCM_Products.IYSTAT", "vwVIP_CCM_Products.ProdID", "vwVIP_CCM_Products.Supplier", "vwVIP_CCM_Products.Product", "vwVIP_CCM_Products.Supplier_Code"}),
    #"Filtered Rows4" = Table.SelectRows(#"Expanded vwVIP_CCM_Products", each ([vwVIP_CCM_Products.ProdID] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows4",{{"vwVIP_CCM_Products.IYSTAT", "Status"}, {"vwVIP_CCM_Products.ProdID", "Item ID"}, {"vwVIP_CCM_Products.Supplier", "Supplier"}, {"vwVIP_CCM_Products.Product", "CDC Description"}, {"vwVIP_CCM_Products.Supplier_Code", "SRS Code"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Item ID", "UPC/GTIN", "Status", "Supplier", "Description", "CDC Description", "Retail Runs Thru", "Display Start", "Display End", "SRS Code"})
in
    #"Reordered Columns"

待连接查询（SQL数据源） #

let
    Source = Sql.Database("REMOVED", "REMOVED"),
    dbo_vwVIP_CCM_Products = Source{[Schema="dbo",Item="vwVIP_CCM_Products"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_vwVIP_CCM_Products,{{"UPC_Retail", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "UPC_Retail_Trimmed", each Text.RemoveRange([UPC_Retail],Text.Length([UPC_Retail])-1)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([UPC_Retail_Trimmed] <> "")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"UPC_Retail_Trimmed", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "ProdID", each Text.PadStart(Text.From([Item]), 5, "0")),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"IYSTAT", "Item", "ProdID", "Supplier", "Product", "On_Hand", "On_Order", "Seasonal_Flag", "UPC_Retail", "Supplier_Code", "UPC_Retail_Trimmed"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Item"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"IYSTAT", "ProdID", "Supplier", "Product", "On_Hand", "On_Order", "Seasonal_Flag", "UPC_Retail", "UPC_Retail_Trimmed", "Supplier_Code"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"On_Hand", "On_Order", "Seasonal_Flag"})
in
    #"Removed Columns1"

排查方向与解决办法 #

可能原因 #

1. 缓存元数据冲突：长期运行后Power Query缓存的类型信息与实际数据不匹配，表面显示Int64.Type但底层存在隐性不一致
2. SharePoint文件隐性变更：源Excel文件更新后，UPC列出现不可见字符、格式标记，导致Promote Headers后列类型存在潜在问题
3. 嵌套合并的隐性错误：Table.NestedJoin生成的嵌套表中，部分行存在未触发的错误，展开后全列被标记错误

解决步骤 #

1. 清理缓存重启

• 关闭Power BI，删除C:\Users\[你的用户名]\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces下的所有文件夹
• 重启后重新刷新数据，排除缓存问题

2. 强化UPC字段验证

在主查询的Changed Type步骤后添加以下代码，过滤无效UPC值：

#"Validated UPC" = Table.SelectRows(#"Changed Type", each try Number.IsInteger([UPC/GTIN]) otherwise false),
#"Replace UPC Errors" = Table.ReplaceErrorValues(#"Validated UPC", {{"UPC/GTIN", null}})

同时在SQL查询的Changed Type1步骤后添加相同验证，确保双方UPC字段无转换错误

3. 替换嵌套合并为直接合并

将主查询中的Table.NestedJoin替换为直接合并，避免嵌套表的隐性错误：

#"Merged Queries" = Table.MergeQueries(#"Removed Columns", vwVIP_CCM_Products, {"UPC/GTIN"}, {"UPC_Retail_Trimmed"}, JoinKind.LeftOuter)

重新展开合并列，测试是否解决问题

4. 检查源文件格式

• 打开SharePoint上的Excel源文件，检查UPC/GTIN列是否存在文本格式数字、不可见空格
• 使用Excel的=CLEAN()和=TRIM()函数清理UPC列，重新上传后刷新数据

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