excel – PowerQuery – How can i make my queries more compact or a better way to do show the same results?

Im wondering how i could make my queries be less queries. I have not created the queries inside Advanced Editor, but Ive started to look at this and try to use it more.

I have a database called Source_VSLösenord, which has all tickets from a department, and another database called Source_Members which has both inactive and active members. Im filtering out for each category (Melior, PMO, Obstetrix, Orbit), and then filtering for each type (Created, Resolved, Handled) done by each member, and adding a column that defines which type it is for example: Created_Melior, Resolved_Melior .

Excluding Source, i use 9 queries to then append these to 1. So my question is, can you give/educate me 1 or a few better ways to do this?

I want to learn more, Im just a “intermediate” beginner here, so i have a follow up question as well which is how can i convert the column “Month” to display monthname instead of numbers inside the Advanced Editor?

One more thing, i have a tricky task which havent had luck to find about in the webb. In query Handled_PMO im splitting specific strings with timestamp, i need to count as 1 row if the same individual entered a log within 10 minutes in “this” ticket.

I couldnt find upload-button so here below is the code:

---------Source: VSLösenord---------------------------
let
    Source = Excel.Workbook(File.Contents("C:UserskadirdilOneDrive - TietoevryThe LedgerThe Book Of VSLösenordshanteringSource0_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"

Leave a Comment