cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Tabular format transformation

Hi All

i have two data format and i would appreciate your support to get it transformed to tabular format.

 

data 1

 

i receive this format

 

tarekkhalefa_0-1660810001766.png

 

Data 1 desried format

 

tarekkhalefa_1-1660810073766.png

 

Data 2 format

 

tarekkhalefa_2-1660810136333.png

 

Data 2 desired format

 

tarekkhalefa_3-1660810189946.png

 

2 ACCEPTED SOLUTIONS
Luis98
Resolver II
Resolver II

Hi @Anonymous!

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

Table 1:

Luis98_0-1660817358304.png

Table 2:

Luis98_1-1660817373546.png

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.😁

View solution in original post

mussaenda
Super User
Super User

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

View solution in original post

10 REPLIES 10
mussaenda
Super User
Super User

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

Anonymous
Not applicable

@mussaenda thanks alot but i'm curious to know, how did to approach the source 😊

Anonymous
Not applicable

@Luis98  thanks for your support and i would appreciate if you can help me in the below to complete the full picture of the report.

 

the below is the data that i need to calculate from to produce the 2nd table with a certain condidtions

 

Table Data

tarekkhalefa_0-1661099017641.png

 

 

This is advisor evaluation score card definition

Transaction: transaction number

Advisor ID : advisor ref. number in system

Severity : severity of scored evaluation

Attribute : score evaluation category

Sub=attribute : score evaluation sub-category

Score : 1 means fail, and blank means Pass

 

I just need to extract the below table or to be a measure so i can use it per any dimension (date, agent...)

tarekkhalefa_1-1661099111232.png

 

Conditions

With the below considerations

 

EC : whenever there is failed attribute within the same transaction, it should be calculated as one regarding the number of failed attributes

NC : it is the opposite, it counted based on attributes level so whenever there is failed attributes, it should be counted. But if advisor failed 2 sub-attributes in the same attribute, it should be counted as 1.

 

Good morning @Anonymous,

 

Sorry i was busy. If i understand rightly you need count EC when Severity is EU and Score is blank and NC when Severity is NC and Score is 1, but you cant count 2 times the same attribute?

Anonymous
Not applicable

@Luis98  Exactly correct for NC but the EC, whenever there is failed attribute ( score 1), it should be counted as onle regardless the count of failed attributes. i hope i could explained it in a clear way. i'm a bit bad in explaination 😉

Anonymous
Not applicable

@mussaenda 

@Luis98  

 

can i have your support

Luis98
Resolver II
Resolver II

Hi @Anonymous!

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

Table 1:

Luis98_0-1660817358304.png

Table 2:

Luis98_1-1660817373546.png

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.😁

Anonymous
Not applicable

@Luis98  Thanks a lot, Table 1 works and i understood the process but table 2 didn't 🙄. if you could elaboarte the approach more, will be appreciated.

Anonymous
Not applicable

Hi Luis

Thanks a lot for you prompt response, i will try it and i hope it will fix my challenge. i will get back to you.

 

Good morning @Anonymous, 

Maybe the instructions for the second table were not so clear, my bad sorry. I will try explain you what i did it to get the date and the Agent group in a different columns, and i hope with this you can continue with the rest.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "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",{"Column1", "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 Rows" = Table.SelectRows(#"Added Index", each ([Index] <> 1)),
    #"Filled Down2" = Table.FillDown(#"Filtered Rows",{"Column8"})
in
    #"Filled Down2"

Ok, let´s explain it:

The first to steps are the source and the changed type, there are no more.

The steps Added custom and added custom1 are simply creating two columns with null values, we will need them in the follow steps to fill down date and agent group.

The next steps are transposed table and fill down the columns of agent group and date.

Then we transpose again and we would have our two null columns with the two first rows two values: date and agent group.

Then we would have to fill down the column as you wish, and you would get the column of the agent group.

To get the column with the date you would need to delete de second row, where is the agent group. For this we create a index column and filter to delete the second row.

Then you could fill down the other null column and you would get your date column.

 

I hope these instructions help you to understand the proccess. If you have doubts still ask me!😁

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors