Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Tabular format transformation

‎08-18-2022 01:14 AM

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

 

Labels:
Message 1 of 11
1,524 Views
0
2 ACCEPTED SOLUTIONS
Luis98
Resolver II
Resolver II

‎08-18-2022 03:16 AM

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

Message 2 of 11
1,482 Views
mussaenda
Super User
Super User

‎08-22-2022 12:30 AM

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

Message 10 of 11
1,422 Views
0
10 REPLIES 10
mussaenda
Super User
Super User

‎08-22-2022 12:30 AM

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

Message 10 of 11
1,423 Views
0
Anonymous
Not applicable
In response to mussaenda

‎08-22-2022 04:46 AM

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

Message 11 of 11
1,409 Views
0
Anonymous
Not applicable

‎08-21-2022 09:30 AM

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

 

Message 6 of 11
1,434 Views
0
Luis98
Resolver II
Resolver II
In response to Anonymous

‎08-22-2022 06:22 AM

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?

Message 8 of 11
1,394 Views
0
Anonymous
Not applicable
In response to Luis98

‎08-22-2022 08:05 AM

@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 😉

Message 9 of 11
1,388 Views
0
Anonymous
Not applicable
In response to Anonymous

‎08-22-2022 04:53 AM

@mussaenda 

@Luis98  

 

can i have your support

Message 7 of 11
1,405 Views
0
Luis98
Resolver II
Resolver II

‎08-18-2022 03:16 AM

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

Message 2 of 11
1,483 Views
Anonymous
Not applicable
In response to Luis98

‎08-19-2022 03:44 PM

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

Message 5 of 11
1,467 Views
0
Anonymous
Not applicable
In response to Luis98

‎08-18-2022 11:53 AM

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.

 

Message 3 of 11
1,473 Views
0
Luis98
Resolver II
Resolver II
In response to Anonymous

‎08-20-2022 04:15 AM

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

Message 4 of 11
1,452 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All
Top Solution Authors
View All