Power Query合并扩展表报错：DataFormat.Error无法转文本为数字

问题详情 #

• 多文件聚合数据后，通过Cost Center列与Houston Cost Center表做全外连接，目标为特定客户的成本中心填充HOUSTON JOB GROUP分类
• 部分行无Cost Center值，部分无需分类的Cost Center未在匹配表中
• 此前查询运行正常，因某无关表数据缺失刷新后，在扩展合并后的嵌套表步骤触发报错：DataFormat.Error: We couldn't convert to a Number，提示无法将文本"Allied and Nursing"转换为数字，但该文本并未出现在合并关联的Cost Center列中
• 已执行排查操作：将Cost Center列转为文本格式、关闭全局自动类型检测，且前置步骤未设置日期/数字强制格式，但报错仍存在

关键表格说明 #

• 合并前主表：包含Cost Center、Facility、Inv. Date等核心字段，Cost Center列存在数值及空值
• 待合并匹配表：含两列，HOUSTON COST CENTER（用于匹配的键）和HOUSTON JOB GROUP（需填充的分类值）
• 报错触发点：在Expanded Houston Cost Center步骤（扩展合并生成的嵌套表列时）抛出错误

查询代码 #

let
Source = Table.Combine({#"Timecards 2019-09", #"Expense 2021-01", #"Swedish 2018-11", #"Summit 2018-12", #"TMC 2018-11", #"Manual Inv 2020-02", #"Admin Fee 2020-04"}),
#"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Merged Queries - Facilities" = Table.NestedJoin(#"Removed Blank Rows", {"Facility"}, #"Facility Table", {"Facility Name"}, "Facility Table", JoinKind.LeftOuter),
#"Expanded Facility Table" = Table.ExpandTableColumn(#"Merged Queries - Facilities", "Facility Table", {"System Name", "System_HWLID", "Facility Name", "Facility ID", "Facility_HWLID", "State", "Facility Original"}, {"System Name", "System_HWLID", "Facility Name", "Facility ID", "Facility_HWLID", "State", "Facility Original"}),
#"Merged Queries - Houston Cost Center" = Table.NestedJoin(#"Expanded Facility Table", {"Cost Center"}, #"Houston Cost Center", {"HOUSTON COST CENTER"}, "Houston Cost Center", JoinKind.FullOuter),
#"Expanded Houston Cost Center" = Table.ExpandTableColumn(#"Merged Queries - Houston Cost Center", "Houston Cost Center", {"HOUSTON JOB GROUP"}, {"HOUSTON JOB GROUP"}),
#"Add Refresh Date" = Table.AddColumn(#"Expanded Houston Cost Center", "Refresh Date", each DateTime.LocalNow()),
#"Inserted Age" = Table.AddColumn(#"Add Refresh Date", "Age", each Date.From(DateTime.LocalNow()) - [Inv. Date], type duration),
#"Merged Queries - Personnel Table" = Table.NestedJoin(#"Inserted Age", {"Job Group", "System Name", "Facility Name"}, #"Personnel Table", {"Job Group ", "System Name", "Facility"}, "Personnel Table", JoinKind.LeftOuter),
#"Expanded Personnel Table" = Table.ExpandTableColumn(#"Merged Queries - Personnel Table", "Personnel Table", {"CLIENT GROUP", "ACCOUNT MANAGER", "SALES", "BILLING SPECIALIST"}, {"CLIENT GROUP", "ACCOUNT MANAGER", "SALES", "BILLING SPECIALIST"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Personnel Table",{{"Billing Period Start", type date}, {"Inv. Date", type date}, {"Shift Date", type date}, {"Payment Date 1", type date}, {"Payment Date 2", type date}, {"Payment Date 3", type date}, {"Payment Date 4", type date}, {"Payment Date 5", type date}, {"Payment Date 6", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([CLIENT GROUP] <> "SOG")),
#"Added Aging Buckets" = Table.AddColumn(#"Filtered Rows", "Aging Buckets", each if [Age] >= #duration(180, 0, 0, 0) then "6 - 180 Days or More" else if [Age] >= #duration(120, 0, 0, 0) then "5 - 120-179 Days Aged" else if [Age] >= #duration(90, 0, 0, 0) then "4 - 90-119 Days Aged" else if [Age] >= #duration(60, 0, 0, 0) then "3 - 60-89 Days Aged" else if [Age] >= #duration(30, 0, 0, 0) then "2 - 30-59 Days Aged" else "1 - Within 30 Days"),
#"Added Max Paid Date" = Table.AddColumn(#"Added Aging Buckets", "Max Paid Date", each List.Max({[Payment Date 1], [Payment Date 2], [Payment Date 3], [Payment Date 4], [Payment Date 5], [Payment Date 6]})),
#"Inserted Date Subtraction" = Table.AddColumn(#"Added Max Paid Date", "Subtraction", each Duration.Days([Max Paid Date] - [Inv. Date]), Int64.Type),
#"Renamed Date Subtraction to Days To Pay" = Table.RenameColumns(#"Inserted Date Subtraction",{{"Subtraction", "Days To Pay"}}),
#"Grouped Rows" = Table.Group(#"Renamed Date Subtraction to Days To Pay", {"CLIENT GROUP"}, {{"Most Recent Invoice Date", each List.Max([Inv. Date]), type nullable date}, {"First Invoice Date", each List.Min([Inv. Date]), type nullable date}, {"Original Table", each _, type table [Job Group=text, #"HWL Invoice#"=text, Billing Period Start=nullable date, Inv. Date=nullable date, Agency=text, Agency_HWLID=number, #"Agency Invoice#"=text, Facility=text, Cost Center=number, Staff Name=text, Job Title=text, Shift Date=nullable date, Charge Type=text, Invoice Total Amount=number, Total Received Payments=number, Invoice Remaining Balance=number, #"Agency Invoice# - Short Dash"=text, Payment ID 1=nullable text, #"CK# 1"=nullable text, Payment Date 1=nullable date, Payment Amount 1=nullable number, Payment ID 2=any, #"CK# 2"=any, Payment Date 2=nullable date, Payment Amount 2=any, Payment ID 3=any, #"CK# 3"=any, Payment Date 3=nullable date, Payment Amount 3=any, Payment ID 4=any, #"CK# 4"=any, Payment Date 4=nullable date, Payment Amount 4=any, Payment ID 5=any, #"CK# 5"=any, Payment Date 5=nullable date, Payment Amount 5=any, Payment ID 6=any, #"CK# 6"=any, Payment Date 6=nullable date, Payment Amount 6=any, #"HWL CIN#"=text, Worksheet Tab=text, Department=any, Unit Cost=any, SOG Period=any, Units=any, #"Units (HH:mm)"=any, System Name=text, System_HWLID=number, Facility Name=text, Facility ID=text, Facility_HWLID=number, State=text, Facility Original=any, HOUSTON JOB GROUP=nullable text, Refresh Date=datetime, Age=duration, CLIENT GROUP=text, ACCOUNT MANAGER=text, SALES=text, BILLING SPECIALIST=text, Aging Buckets=text, Max Paid Date=nullable date, Days To Pay=number]}}),
#"Expanded Original Table" = Table.ExpandTableColumn(#"Grouped Rows", "Original Table", {"Job Group", "HWL Invoice#", "Billing Period Start", "Inv. Date", "Agency", "Agency_HWLID", "Agency Invoice#", "Facility", "Cost Center", "Staff Name", "Job Title", "Shift Date", "Charge Type", "Invoice Total Amount", "Total Received Payments", "Invoice Remaining Balance", "Agency Invoice# - Short Dash", "Payment ID 1", "CK# 1", "Payment Date 1", "Payment Amount 1", "Payment ID 2", "CK# 2", "Payment Date 2", "Payment Amount 2", "Payment ID 3", "CK# 3", "Payment Date 3", "Payment Amount 3", "Payment ID 4", "CK# 4", "Payment Date 4", "Payment Amount 4", "Payment ID 5", "CK# 5", "Payment Date 5", "Payment Amount 5", "Payment ID 6", "CK# 6", "Payment Date 6", "Payment Amount 6", "HWL CIN#", "Worksheet Tab", "Department", "Unit Cost", "SOG Period", "Units", "Units (HH:mm)", "System Name", "System_HWLID", "Facility Name", "Facility ID", "Facility_HWLID", "State", "Facility Original", "HOUSTON JOB GROUP", "Refresh Date", "Age", "CLIENT GROUP", "ACCOUNT MANAGER", "SALES", "BILLING SPECIALIST", "Aging Buckets", "Max Paid Date", "Days To Pay"}, {"Original Table.Job Group", "Original Table.HWL Invoice#", "Original Table.Billing Period Start", "Original Table.Inv. Date", "Original Table.Agency", "Original Table.Agency_HWLID", "Original Table.Agency Invoice#", "Original Table.Facility", "Original Table.Cost Center", "Original Table.Staff Name", "Original Table.Job Title", "Original Table.Shift Date", "Original Table.Charge Type", "Original Table.Invoice Total Amount", "Original Table.Total Received Payments", "Original Table.Invoice Remaining Balance", "Original Table.Agency Invoice# - Short Dash", "Original Table.Payment ID 1", "Original Table.CK# 1", "Original Table.Payment Date 1", "Original Table.Payment Amount 1", "Original Table.Payment ID 2", "Original Table.CK# 2", "Original Table.Payment Date 2", "Original Table.Payment Amount 2", "Original Table.Payment ID 3", "Original Table.CK# 3", "Original Table.Payment Date 3", "Original Table.Payment Amount 3", "Original Table.Payment ID 4", "Original Table.CK# 4", "Original Table.Payment Date 4", "Original Table.Payment Amount 4", "Original Table.Payment ID 5", "Original Table.CK# 5", "Original Table.Payment Date 5", "Original Table.Payment Amount 5", "Original Table.Payment ID 6", "Original Table.CK# 6", "Original Table.Payment Date 6", "Original Table.Payment Amount 6", "Original Table.HWL CIN#", "Original Table.Worksheet Tab", "Original Table.Department", "Original Table.Unit Cost", "Original Table.SOG Period", "Original Table.Units", "Original Table.Units (HH:mm)", "Original Table.System Name", "Original Table.System_HWLID", "Original Table.Facility Name", "Original Table.Facility ID", "Original Table.Facility_HWLID", "Original Table.State", "Original Table.Facility Original", "Original Table.HOUSTON JOB GROUP", "Original Table.Refresh Date", "Original Table.Age", "Original Table.CLIENT GROUP", "Original Table.ACCOUNT MANAGER", "Original Table.SALES", "Original Table.BILLING SPECIALIST", "Original Table.Aging Buckets", "Original Table.Max Paid Date", "Original Table.Days To Pay"}),
Custom2 = #"Renamed Date Subtraction to Days To Pay",
#"Changed Type" = Table.TransformColumnTypes(Custom2,{{"Job Group", type text}, {"HWL Invoice#", type text}, {"Agency", type text}, {"Billing Period Start", type date}, {"Inv. Date", type date}, {"Agency_HWLID", type text}, {"Facility", type text}, {"Department", type text}, {"Cost Center", type text}, {"Staff Name", type text}, {"Job Title", type text}, {"Shift Date", type date}, {"Charge Type", type text}, {"Units", type text}, {"Units (HH:mm)", type text}, {"Unit Cost", Currency.Type}, {"Invoice Total Amount", Currency.Type}, {"Total Received Payments", Currency.Type}, {"Invoice Remaining Balance", Currency.Type}, {"SOG Period", type text}, {"Agency Invoice# - Short Dash", type text}, {"Payment ID 1", type text}, {"CK# 1", type text}, {"Payment ID 2", type text}, {"CK# 2", type text}, {"Payment ID 3", type text}, {"CK# 3", type text}, {"Payment ID 4", type text}, {"CK# 4", type text}, {"HWL CIN#", type text}, {"System Name", type text}, {"System_HWLID", type text}, {"Facility ID", type text}, {"Facility_HWLID", type text}, {"State", type text}, {"HOUSTON JOB GROUP", type text}, {"Payment Date 1", type date}, {"Payment Date 2", type date}, {"Payment Date 3", type date}, {"Payment Date 4", type date}, {"Payment Amount 1", Currency.Type}, {"Payment Amount 2", Currency.Type}, {"Payment Amount 3", Currency.Type}, {"Payment Amount 4", Currency.Type}, {"Refresh Date", type date}, {"Age", Int64.Type}, {"Payment Date 5", type date}, {"Payment Date 6", type date}, {"Payment Amount 5", Currency.Type}, {"Payment Amount 6", Currency.Type}, {"Max Paid Date", type date}})
in
#"Changed Type"

预期结果 #

保留所有原始行，新增HOUSTON JOB GROUP列，匹配到Cost Center的行填充对应分类值，未匹配或无Cost Center的行该列为空

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