Query 错误：We couldn't parse the input provided as a Date value.

相关文章推荐

帅气的洋葱 · VSCode报错 Unexpected ...· 1 月前 ·

奔跑的鸭蛋 · 在onChange事件中获取按钮值 - · 1 月前 ·

奋斗的企鹅 · JS---DOM---点击操作---part ...· 1 月前 ·

重感情的机器人 · Not able to create ...· 7 月前 ·

爱看球的人字拖 · 人工智能能不能通过机器学习，预测未来股票走势 ...· 1 年前 ·

安静的香烟 · nltk.download('punkt') ...· 1 年前 ·

豪情万千的上铺 · IDictionary<tkey> ...· 1 年前 ·

1. 我将一个excel 表格导入到query 中，然后做了一些操作，再导出成表格，就提示了错误: We couldn't parse the input provided as a Date value.
2. 代码如下：

Source = Folder.Files("C:\Users\unhua\Downloads\ISOM"),

#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "3AD02800" and [Name] <> "[ISOM.012] Delivery Work Orders CSB-2020.2.xlsx")),

#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),

#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),

#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Custom"}),

#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),

#"Removed Other Columns2" = Table.SelectColumns(#"Expanded Custom",{"Data"}),

#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns2", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42"}),

#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Enterprise Code", type text}, {"Seller Code", type text}, {"Work Order Number", Int64.Type}, {"Work Order Status", type text}, {"Sales Order Number", Int64.Type}, {"Sales Order Status", type text}, {"Hub Code", type text}, {"Ship Node Code", type text}, {"Delivery Service Type", type text}, {"Delivery Service", type text}, {"TSP Description", type text}, {"Network ID", type text}, {"Pick-up Point ID", type text}, {"Pick-up Point Name", type text}, {"Pick-up Point Zip Code", type text}, {"Payment Type", type text}, {"First Name in Address", type text}, {"Last Name in Address", type text}, {"Address Line 1", type text}, {"Zip Code", Int64.Type}, {"City", type text}, {"Work Order Creation Date (Fulfilment Node time zone)", type datetime}, {"Work Order Delivered Date (Customer Delivery time zone)", type datetime}, {"Sales Order Creation Date (Seller Code time zone)", type datetime}, {"Original Promised Delivery Date (Customer Delivery time zone)", type datetime}, {"Promised Delivery Date (Customer Delivery time zone)", type datetime}, {"Dispatched from Ship Node Date (Fulfillment time zone)", type datetime}, {"Received At The Hub Date (Fulfillment Node time zone)", type datetime}, {"Load on Delivery Truck Date (Fulfillment time zone)", type datetime}, {"Ready for Pick-up (Customer Delivery time zone)", type datetime}, {"Actual WO Delivery Timestamp (Customer Delivery time zone)", type datetime}, {"Days between Ready for Pick-up to Picked up by Customer", type text}, {"Total Work Order Weight", type number}, {"Weight UoM", type text}, {"Total Work Order Volume (m3)", type number}, {"Total Work Order Price Incl. VAT", type number}, {"Currency", type text}, {"Associated Return Order Receiving Node", type text}, {"Total Number of Work Order Scans with Delivery Failed", Int64.Type}, {"Total Number of Work Order Scans with Delivery Failed by Customer", Int64.Type}, {"Total Number of Work Order Scans with Delivery Failed by TSP", Int64.Type}, {"Total Number of Work Order Scans with Delivery Failed by IKEA", Int64.Type}}),

#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Sales Order Creation Date (Seller Code time zone)", type date}}),

#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each [#"Sales Order Creation Date (Seller Code time zone)"] >= #date(2019, 10, 1) and [#"Sales Order Creation Date (Seller Code time zone)"] <= #date(2019, 10, 31)),

#"Removed Other Columns3" = Table.SelectColumns(#"Filtered Rows1",{"Sales Order Number", "Zip Code"}),

#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns3", {"Sales Order Number"})

#"Removed Duplicates"

我们需要向您确认一些信息以更好地了解该问题：
1. 您代码的导入步骤是否为 Data > From Text/CSV > file origin 1252: Western European (Windows) ？
2. 您提到您做了一些操作，能否详细说明一下？
3. 对于该错误信息，如果可以的话，请截张图片给我们。
4. 您的 Office 版本号是多少？在打开 Excel 后，点击 File > Account ，请将 Product Information 下的内容截图并提供给我们。
感谢您的理解与配合。
Madoc

-----------------------

* Beware of scammers posting fake support numbers here.

* Kindly Mark and Vote this reply if it helps, as it will be beneficial to more community members reading here.

1. 数据导入的步骤为data > from folder ,然后将多个工作簿进行合并的，合并操作如下：

#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "3AD02800" and [Name] <> "[ISOM.012] Delivery Work Orders CSB-2020.2.xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),

#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),

#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Custom"}),

#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),

#"Removed Other Columns2" = Table.SelectColumns(#"Expanded Custom",{"Data"}),

#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),

合并后，再对每列设置格式。

2. 其中一列为“Sales Order Creation Date (Seller Code time zone)”，对这列进行筛选，筛选出日期在2019.10-2019.11的数据。代码如下：

3. 然后删除不需要的列和重复项。

4. 最后导出时，就提示了错误。

通过您的描述，该问题的产生与合并工作簿时具体工作簿的值有关。
因为 Microsoft Community 论坛主要处理用户在使用 Office 365 过程中遇到的一些即用的问题。对于您遇到的这个问题，它牵扯到 Power Query 和工作簿的具体内容，因为我们的资源有限，无法就此给出更深层次的建议和可能的方案。
因此，我们真诚地建议您将该问题发送到 Power Query 论坛中，有关 Power Query 的问题都可以在那里探讨、获得帮助。 Power Query 论坛的支持工程师将尽他们最大的努力协助您解决问题。
非常感谢您的理解，祝您工作顺利。
Madoc Huang

-----------------------

* Beware of scammers posting fake support numbers here.

* Kindly Mark and Vote this reply if it helps, as it will be beneficial to more community members reading here.