PowerQuery-如何使我的查询更加紧凑或更好地显示出相同的结果？

Question

我想知道我如何使我的查询更少。 我没有在高级编辑器中创建查询，但是IVE开始查看此问题并尝试使用更多。

我有一个名为source_vslösenord的数据库，该数据库具有部门的所有门票，还有另一个称为source_members的数据库，该数据库具有非活动成员和活动成员。 IM对每个类别（Melior，pmo，Obstetix，Orbit）过滤出来，然后对每个类型（创建，解决，处理）进行过滤，并添加一个定义哪种类型的列，例如：created_melior，resolved_melorior，resolved_melior 。

除了来源外，我使用9个查询将其附加到1。因此，我的问题是，您能给我/教育1或一些更好的方法来做到这一点吗？

我想了解更多，我只是在这里的“中间”初学者，所以我还有一个后续问题，哪个是如何将“月”“月”转换为“月名”而不是高级编辑器中的数字？

还有一件事，我有一项棘手的任务，在韦伯（Webb）中还没有发现这是一个棘手的任务。在查询处理的_pmo im用时间戳将特定的字符串分解为1行，如果同一个人在10分钟内进入“此”票证中的日志，我需要将其算作1行。

我找不到上传按钮，所以以下是代码：

---------Source: VSLösenord---------------------------
let
    Source = Excel.Workbook(File.Contents("C:\Users\kadirdil\OneDrive - Tietoevry\The Ledger\The Book Of VS\Lösenordshantering\Source\00_Lösenord_280422.xlsx"), null, true),

    Source_VSLösenord_Table = Source{[Item="Source_VSLösenord",Kind="Table"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Source_VSLösenord_Table,{{"Category", type text}, {"Subcategory", type text}, {"Number", type text}, {"Created", type datetime}, {"Resolved", type datetime}, {"Service", type text}, {"Configuration item", type text}, {"Short description", type text}, {"Description", type text}, {"Affected User", type text}, {"Reported by", type text}, {"Assigned to", type text}, {"Created by group", type text}, {"Created by", Int64.Type}, {"Opened by", type text}, {"Assignment group", type text}, {"Closed by", type text}, {"Resolved by", type text}, {"Resolution code", type text}, {"Resolution notes", type text}, {"Additional comments", type text}, {"Comments and Work notes", type text}, {"Work notes", type text}, {"On Hold Count", Int64.Type}, {"On hold reason", type any}, {"Reassignment count", Int64.Type}, {"Reopen count", Int64.Type}}),

    #"Create Fixed Service" = Table.AddColumn(#"Changed Type", "Fixed Service", each if Text.Contains([Service], "Melior") then "Melior" else if Text.Contains([Service], "Orbit") then "Orbit" else if Text.Contains([Service], "PMO") then "PMO" else if Text.Contains([Service], "Obstetrix") then "Obstetrix" else "Null"),

    #"Reordered Columns" = Table.ReorderColumns(#"Create Fixed Service",{"Fixed Service", "Number", "Category", "Subcategory", "Created", "Resolved", "Service", "Configuration item", "Short description", "Description", "Affected User", "Reported by", "Assigned to", "Created by group", "Created by", "Opened by", "Assignment group", "Closed by", "Resolved by", "Resolution code", "Resolution notes", "Additional comments", "Comments and Work notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),

    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Category] = "Fråga / Stöd") and ([Subcategory] = "Lösenord")),

    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Reopen count", Order.Descending}}),
    
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Number"})
in
    #"Removed Duplicates"
    

---------Source: Members---------------------------
let
    Source = Excel.CurrentWorkbook(){[Name="Source_Members"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Displayname", type text}, {"Name", type text}, {"Active", type text}})
in
    #"Changed Type"
    

---------Query: Created_Melior---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered Melior" = Table.SelectRows(Source, each ([Fixed Service] = "Melior")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Melior",{"Category", "Subcategory", "Resolved", "Service", "Configuration item", "Affected User", "Reported by", "Closed by", "Resolved by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each "Melior, Created"),
    Essential = Table.RemoveColumns(#"Added Count Type",{"Short description", "Description", "Assigned to", "Created by group", "Created by", "Assignment group", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Essential,{"Fixed Service", "Count Type", "Number", "Created", "Opened by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Created", "Date"}, {"Opened by", "Agent"}})
in
    #"Renamed Columns"
    
    
---------Query: Created_PMO---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered PMO" = Table.SelectRows(Source, each ([Fixed Service] = "PMO")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered PMO",{"Category", "Subcategory", "Resolved", "Service", "Configuration item", "Affected User", "Reported by", "Closed by", "Resolved by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each "PMO, Created"),
    Essential = Table.RemoveColumns(#"Added Count Type",{"Short description", "Description", "Assigned to", "Created by group", "Created by", "Assignment group", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Essential,{"Fixed Service", "Count Type", "Number", "Created", "Opened by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Created", "Date"}, {"Opened by", "Agent"}})
in
    #"Renamed Columns"
    
    
---------Query: Created_Obstetrix---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered Obstetrix" = Table.SelectRows(Source, each ([Fixed Service] = "Obstetrix")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Obstetrix",{"Category", "Subcategory", "Resolved", "Service", "Configuration item", "Affected User", "Reported by", "Closed by", "Resolved by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each "Obstetrix, Created"),
    Essential = Table.RemoveColumns(#"Added Count Type",{"Short description", "Description", "Assigned to", "Created by group", "Created by", "Assignment group", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Essential,{"Fixed Service", "Count Type", "Number", "Created", "Opened by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Created", "Date"}, {"Opened by", "Agent"}})
in
    #"Renamed Columns"
    
    
---------Query: Created_Orbit---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered Orbit" = Table.SelectRows(Source, each ([Fixed Service] = "Orbit")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Orbit",{"Category", "Subcategory", "Resolved", "Service", "Configuration item", "Affected User", "Reported by", "Closed by", "Resolved by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each "Orbit, Created"),
    Essential = Table.RemoveColumns(#"Added Count Type",{"Short description", "Description", "Assigned to", "Created by group", "Created by", "Assignment group", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Essential,{"Fixed Service", "Count Type", "Number", "Created", "Opened by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Created", "Date"}, {"Opened by", "Agent"}})
in
    #"Renamed Columns"
    

---------Query: Resolved_Melior---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered Melior" = Table.SelectRows(Source, each ([Fixed Service] = "Melior")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Melior",{"Category", "Subcategory", "Created", "Service", "Configuration item", "Short description", "Description", "Affected User", "Reported by", "Created by group", "Created by", "Opened by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each "Melior, Resolved"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Count Type",{"Assigned to", "Assignment group", "Closed by", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Fixed Service", "Count Type", "Number", "Resolved", "Resolved by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Resolved", "Date"}, {"Resolved by", "Agent"}})
in
    #"Renamed Columns"
    
    
---------Query: Resolved_PMO---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered PMO" = Table.SelectRows(Source, each ([Fixed Service] = "PMO")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered PMO",{"Category", "Subcategory", "Created", "Service", "Configuration item", "Short description", "Description", "Affected User", "Reported by", "Created by group", "Created by", "Opened by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each "PMO, Resolved"),
    Custom1 = Table.RemoveColumns(#"Added Count Type",{"Assigned to", "Assignment group", "Closed by", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Custom1,{"Fixed Service", "Count Type", "Number", "Resolved", "Resolved by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Resolved", "Date"}, {"Resolved by", "Agent"}})
in
    #"Renamed Columns"
    
    
---------Query: Resolved_Obstetrix---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered Obstetrix" = Table.SelectRows(Source, each ([Fixed Service] = "Obstetrix")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Obstetrix",{"Category", "Subcategory", "Created", "Service", "Configuration item", "Short description", "Description", "Affected User", "Reported by", "Created by group", "Created by", "Opened by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each "Obstetrix, Resolved"),
    Custom1 = Table.RemoveColumns(#"Added Count Type",{"Assigned to", "Assignment group", "Closed by", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Custom1,{"Fixed Service", "Count Type", "Number", "Resolved", "Resolved by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Resolved", "Date"}, {"Resolved by", "Agent"}})
in
    #"Renamed Columns"
    
    
---------Query: Resolved_Orbit---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered Orbit" = Table.SelectRows(Source, each ([Fixed Service] = "Orbit")),
    Orbit = Table.RemoveColumns(#"Filtered Orbit",{"Category", "Subcategory", "Created", "Service", "Configuration item", "Short description", "Description", "Affected User", "Reported by", "Created by group", "Created by", "Opened by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Add Column, Count Type" = Table.AddColumn(Orbit, "Count Type", each "Orbit, Resolved"),
    Custom1 = Table.RemoveColumns(#"Add Column, Count Type",{"Assigned to", "Assignment group", "Closed by", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Custom1,{"Fixed Service", "Count Type", "Number", "Resolved", "Resolved by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Resolved", "Date"}, {"Resolved by", "Agent"}})
in
    #"Renamed Columns"


---------Query: Handled_PMO---------------------------
let
    Source = #"Source_VSLösenord",
    #"Filtered PMO" = Table.SelectRows(Source, each ([Fixed Service] = "PMO")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered PMO",{"Category", "Subcategory", "Created", "Resolved", "Service", "Configuration item", "Short description", "Description", "Affected User", "Reported by", "Assigned to", "Created by group", "Created by", "Opened by", "Assignment group", "Closed by", "Resolved by", "Resolution code", "Resolution notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Select List" = Table.AddColumn(#"Removed Columns", "List", each List.Select(Text.Split([Comments and Work notes], "#(lf)"), each not Text.Contains(_, "System") and (Text.EndsWith(_, "(Work notes)") or Text.EndsWith(_, "(Additional comments)")))),
    Distinct = Table.AddColumn(#"Select List", "List.1", each Table.Distinct(Table.FromList(List.Transform ( [List], each Text.Start(_, 19) & ","  & Text.Range(_, 22, Text.PositionOf(_, "(")-22)), null, {"Date", "UserName"}))),
    #"Table Set" = Table.AddColumn(#"Distinct", "List.2", each [List.1]),
    #"Table show Date" = Table.ExpandTableColumn(#"Table Set", "List.1", {"Date"}, {"List.Date"}),
    #"Table show Agent" = Table.ExpandTableColumn(#"Table show Date", "List.2", {"UserName"}, {"List.Agent"}),
    Cleaned = Table.TransformColumns(#"Table show Agent",{{"List.Agent", Text.Clean, type text}}),
    Trimmed = Table.TransformColumns(#"Cleaned",{{"List.Agent", Text.Trim, type text}}),
    #"Added Count Type" = Table.AddColumn(Trimmed, "Count Type", each "PMO, Handled"),
    Essential = Table.RemoveColumns(#"Added Count Type",{"Additional comments", "Comments and Work notes", "Work notes", "List"}),
    #"Reordered Columns" = Table.ReorderColumns(Essential,{"Fixed Service", "Count Type", "Number", "List.Date", "List.Agent"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"List.Date", "Date"}, {"List.Agent", "Agent"}})
in
    #"Renamed Columns"


---------Query: Appended---------------------------
let
    Source = Table.Combine({Grouped_Created_Melior, Grouped_Created_PMO, Grouped_Created_Obstetrix, Grouped_Created_Orbit, Grouped_Handled_PMO, Grouped_Resolved_Melior, Grouped_Resolved_PMO, Grouped_Resolved_Obstetrix, Grouped_Resolved_Orbit}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB"), "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type time}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Date.1", type text}}, "en-GB"), "Date.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1.1", "Date.1.2", "Date.1.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date.1.1", Int64.Type}, {"Date.1.2", Int64.Type}, {"Date.1.3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Date.1.1", "Day"}, {"Date.1.2", "Month"}, {"Date.1.3", "Year"}, {"Date.2", "TimeStamp"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Fixed Service", "Count Type", "Number", "Year", "Month", "Day", "TimeStamp", "Agent"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Agent"}, Source_Members, {"Name"}, "Source_Members", JoinKind.LeftOuter),
    #"Expanded Source_Members" = Table.ExpandTableColumn(#"Merged Queries", "Source_Members", {"Name"}, {"Source_Members.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Source_Members", each ([Source_Members.Name] <> null))
in
    #"Filtered Rows"

Answer 1

如何用仅两个而不是八个替换所有创建的和解决

---------Query: Created
let
    Source = #"Source_VSLösenord",
    #"Filtered" = Table.SelectRows(Source, each [Fixed Service] = "Melior" or [Fixed Service] = "PMO" or [Fixed Service] = "Obstetrix" or [Fixed Service] = "Orbit"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered",{"Category", "Subcategory", "Resolved", "Service", "Configuration item", "Affected User", "Reported by", "Closed by", "Resolved by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each [Fixed Service] & ", Created"),
    Essential = Table.RemoveColumns(#"Added Count Type",{"Short description", "Description", "Assigned to", "Created by group", "Created by", "Assignment group", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(Essential,{"Fixed Service", "Count Type", "Number", "Created", "Opened by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Created", "Date"}, {"Opened by", "Agent"}})
in
    #"Renamed Columns"
    
---------Query: Resolved
let
    Source = #"Source_VSLösenord",
    #"Filtered" = Table.SelectRows(Source, each [Fixed Service] = "Melior" or [Fixed Service] = "PMO" or [Fixed Service] = "Obstetrix" or [Fixed Service] = "Orbit"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered",{"Category", "Subcategory", "Created", "Service", "Configuration item", "Short description", "Description", "Affected User", "Reported by", "Created by group", "Created by", "Opened by", "Resolution code", "Resolution notes", "Work notes", "On Hold Count", "On hold reason", "Reassignment count", "Reopen count"}),
    #"Added Count Type" = Table.AddColumn(#"Removed Columns", "Count Type", each [Fixed Service] & ", Resolved"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Count Type",{"Assigned to", "Assignment group", "Closed by", "Additional comments", "Comments and Work notes"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Fixed Service", "Count Type", "Number", "Resolved", "Resolved by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Resolved", "Date"}, {"Resolved by", "Agent"}})
in
    #"Renamed Columns"