powerquery - 扩展表时 Power Query 缺失值
问题描述
我有一个问题:当我尝试从许多文件中扩展表时,我丢失了一些值。
到目前为止,我发现问题是在查询遇到空表时开始的。
我试图过滤掉空表,但问题仍然存在。
知道什么是问题以及如何解决吗?
询问:
let
Source = Folder.Files("X:\Operations\tutaj\SLA MIAD"),
#"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each Text.Contains([Extension], "xlsx")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "NCR") or Text.Contains([Name], "Printec") or Text.Contains([Name], "PTL")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "2018")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows3" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Sheet")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each Text.Contains([Name], "SLA report")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows4",{"Name", "Item", "Kind", "Hidden"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Content"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Errors", "TestFunction", each FileQuery([Data])),
#"Added Custom1" = Table.AddColumn(#"Invoked Custom Function", "Custom", each Table.IsEmpty([TestFunction])),
#"Filtered Rows6" = Table.SelectRows(#"Added Custom1", each ([Custom] = false)),
#"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}, {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded TestFunction",{"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "Condition", "Reason", "Status", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Created(CET)", type datetime}, {"SLA Target Date(CET)", type datetime}, {"Closed(CET)", type datetime}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Age", each [#"Closed(CET)"]-[#"Created(CET)"]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Age", type duration}}),
#"Filtered Rows5" = Table.SelectRows(#"Changed Type1", each [Order ID] <> null and [Order ID] <> "")
in
#"Filtered Rows5"
解决方案
我认为您的表中存在的标题(您想要扩展的)和您实际扩展的标题存在一些差异。
您已经展开SLACategory
和Order ID
(在屏幕截图的上半部分),但这些应该分别是SLA Category
和OrderId
(在屏幕截图的下半部分)。注意间距和大小写的差异。
当您指定List
要扩展的标题时(在下面之间{
)}
:
#"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}, {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}),
我认为你需要
- 完美地对标题本身进行硬编码(在区分大小写和空格方面)
- 或动态扩展它们,而不是基于一些硬编码列表。
要动态扩展列,您必须执行类似替换此行的操作:
#"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}, {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}),
和:
allHeaders = List.Combine(List.Transform(#"Filtered Rows6"[TestFunction], Table.ColumnNames)),
headersToExpand = List.Distinct(allHeaders),
#"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", headersToExpand),
(您需要单击查询编辑器左上角的“高级编辑器”并替换那里的代码。)
推荐阅读
- reactjs - ESLint 损坏:带有建议的规则必须将 `meta.hasSuggestions` 属性设置为 `true`
- angular - 使用搜索和自动完成选择 Ionic
- r - 数字转换为时间已停止工作在 r
- html - 黄色下划线 CSS 随机未应用于跨度的全宽
- arrays - Excel上的数组函数
- python - 在drf中,url地址没有通过验证
- javascript - 如何将带有 alpha 通道的十六进制颜色字符串转换为 rgba 字符串?
- python - 在前 4 个字符后按字母顺序对 pandas 数据帧进行排序
- javascript - 为什么我的验证不适用于我的选择
- node.js - Node js Server High Ram Usage 使用 Native 集群模式