Tabular format transformation

Hi Anonymous!

I get it these two tables, i am not sure if i understood correctly your ask.

Table 1:

Table 2:

To get these tables i did it the next steps:

Table 1:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4"}),
#"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Headers"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"____Agent ID", Int64.Type}, {"Category_Severity_Attribute_Sub-attribute_Result", type text}, {"Communicate_Non critical_Etiquette_1-Salude_", type text}, {"Communicate_Non critical_Etiquette_2-Gaps_", type text}, {"Communicate_Non critical_Lang._1-Explanation_", type text}, {"Communicate_Non critical_Lang._2-Language_", type text}, {"Communicate_Non critical_Lang._Grammar_", type text}, {"Communicate_Non critical_Communication_1-how to ask_", type text}, {"Communicate_Non critical_Communication_2-Handling_", type text}, {"Communicate_Non critical_Own the case_4-Achknowledge_", type text}, {"Confirm_End user information_Information costumer_1-Provide information_", type text}, {"Confirm_End user information_Information costumer_2-Unnecesary action_", type text}, {"Confirm_End user information_Information costumer_3-Get assisted by expert_", type text}, {"Confirm_End user information_Information costumer_4-Unnecesary escalation done_", type text}, {"Confirm_End user information_Information costumer_5- Complain parked unecesary_", type text}, {"Discover_Business critical_Technical_Complaints_", type text}, {"Discover_Business critical_Technical_Leakage_", type text}, {"Discover_Business critical_Technical_Instruction_", type text}, {"Discover_Business critical_Technical_Proccess_", type text}, {"Discover_Business critical_Technical_Tagging_", type text}, {"Discover_Business critical_Technical_Activities_", type text}, {"Discover_Business critical_Technical_Logging_", type text}, {"Discover_Business critical_Technical_Logging_Date", type datetime}, {"Discover_Business critical_Technical_Logging_Critical error", Int64.Type}, {"Discover_Business critical_Technical_Logging_EUC", Int64.Type}, {"Discover_Business critical_Technical_Logging_BCE", Int64.Type}, {"Discover_Business critical_Technical_Logging_CC", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"Communicate_Non critical_Etiquette_1-Salude_", "Communicate_Non critical_Etiquette_2-Gaps_", "Communicate_Non critical_Lang._1-Explanation_", "Communicate_Non critical_Lang._2-Language_", "Communicate_Non critical_Lang._Grammar_", "Communicate_Non critical_Communication_1-how to ask_", "Communicate_Non critical_Communication_2-Handling_", "Communicate_Non critical_Own the case_4-Achknowledge_", "Confirm_End user information_Information costumer_1-Provide information_", "Confirm_End user information_Information costumer_2-Unnecesary action_", "Confirm_End user information_Information costumer_3-Get assisted by expert_", "Confirm_End user information_Information costumer_4-Unnecesary escalation done_", "Confirm_End user information_Information costumer_5- Complain parked unecesary_", "Discover_Business critical_Technical_Complaints_", "Discover_Business critical_Technical_Leakage_", "Discover_Business critical_Technical_Instruction_", "Discover_Business critical_Technical_Proccess_", "Discover_Business critical_Technical_Tagging_", "Discover_Business critical_Technical_Activities_", "Discover_Business critical_Technical_Logging_"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"____Agent ID", "Category_Severity_Attribute_Sub-attribute_Result", "Discover_Business critical_Technical_Logging_Date", "Discover_Business critical_Technical_Logging_Critical error", "Discover_Business critical_Technical_Logging_EUC", "Discover_Business critical_Technical_Logging_BCE", "Discover_Business critical_Technical_Logging_CC"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4", "Attribute.5"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}, {"Attribute.4", type text}, {"Attribute.5", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute.5"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"____Agent ID", "Agent ID"}, {"Category_Severity_Attribute_Sub-attribute_Result", "Result"}, {"Discover_Business critical_Technical_Logging_Date", "Date"}, {"Discover_Business critical_Technical_Logging_Critical error", "Critical error"}, {"Discover_Business critical_Technical_Logging_EUC", "EUC"}, {"Discover_Business critical_Technical_Logging_BCE", "BCE"}, {"Discover_Business critical_Technical_Logging_CC", "CC"}, {"Attribute.1", "Category"}, {"Attribute.2", "Severity"}, {"Attribute.3", "Attribute"}, {"Attribute.4", "Sub-attribute"}, {"Value", "Score"}})
in
#"Renamed Columns"

Table 2:

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "Totals")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each null),
#"Transposed Table" = Table.Transpose(#"Added Custom1"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column2"}),
#"Transposed Table1" = Table.Transpose(#"Filled Down"),
#"Filled Down1" = Table.FillDown(#"Transposed Table1",{"Column9"}),
#"Added Index" = Table.AddIndexColumn(#"Filled Down1", "Index", 0, 1, Int64.Type),
#"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] <> 1)),
#"Transposed Table2" = Table.Transpose(#"Filtered Rows1"),
#"Filled Down2" = Table.FillDown(#"Transposed Table2",{"Column1"}),
#"Transposed Table3" = Table.Transpose(#"Filled Down2"),
#"Filled Down3" = Table.FillDown(#"Transposed Table3",{"Column8"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down3",{"Column10"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Agent Name", type text}, {"Login ID", Int64.Type}, {"ACD Calls", Int64.Type}, {"Staffed Time", Int64.Type}, {"Talk Time", Int64.Type}, {"AHT", Int64.Type}, {"% CSR Utilization", type number}, {"06/01/2022 0:00:00", type datetime}, {"ABS_FEB22", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"06/01/2022 0:00:00", "Date"}, {"ABS_FEB22", "Agent group"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Agent group", "Agent Name", "Login ID", "ACD Calls", "Staffed Time", "Talk Time", "AHT", "% CSR Utilization"})
in
#"Reordered Columns"

For the first table you only have to transponse table, fill down all headers, merge columns, transponse again and then split column.

For the second table you should play with transponse too to create two new columns with the info you need. If you still dont understand i could try explain it better.😁

Dear Anonymous ,

Since your Data table 1 is solved,

Try this for your second table

Open a blank query, Open Advanced Editor, Select All and delete then paste this code. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYzLCsIwEEV/JQTcSW0mNm2XffgCEbFxVYoErBKsrWjc+PUmsS1FRJgZ7tw5c/McEzzG31WMc5wKVWrNJmQCLsAvJDqXtUKLe/O8aSuKs8N8Fv+HN+JqUtfNWdZolZq3JEWJqKqH1pkSp1N5RFxaiovq0uloyfUcJdkO7ZWs5Eso2dQ2mTdK2Hddrm7iecAIaTfTAXEoteyWG7+7UWAUhlzgeKzljA89N/WHeSE4ev9w1Nx7Dtgwzw8d18NF8QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "Totals")),
    DATE = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains(Text.Upper([Column1]), "AGENT NAME")
then "Date"
else 
#"Filtered Rows"{1}[Column2]),
    #"AGENT GROUP" = Table.AddColumn(DATE, "Custom.1", each if Text.Contains(Text.Upper([Column1]), "AGENT NAME")
then "Agent Group"
else 
#"Filtered Rows"{2}[Column2]),
    #"Removed Top Rows" = Table.Skip(#"AGENT GROUP",3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Agent Name", type text}, {"Login ID", Int64.Type}, {"ACD Calls", Int64.Type}, {"Staffed Time", Int64.Type}, {"Talk Time", Int64.Type}, {"AHT", Int64.Type}, {"%CSR Utilization", type number}, {"Date", type date}, {"Agent Group", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Date", "Agent Group", "Agent Name", "Login ID", "ACD Calls", "Staffed Time", "Talk Time", "AHT", "%CSR Utilization"})
in
    #"Reordered Columns"

Actually the Reordered Column I am not sure if it is necessary on your side.

Hope this helps