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"}),
#"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]),
合并后,再对每列设置格式。
2. 其中一列为“Sales Order Creation Date (Seller Code time zone)”,对这列进行筛选,筛选出日期在2019.10-2019.11的数据。代码如下:
#"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)),
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.