Forum Discussion
Pivot Column NonAgregate Error
Dear v-jingzhang ,
I have finally solved the issue, thanks to your main recommendations.
- Index Column added
- Added calculated column: Index column devided by 2
- Calculated column has been splitted extracting the whole number (before " . " delimiter)
- As a result I've got grouped index column where star/end operations are groupped
- First index column has been deleted
- Pivot Start / End Column with values in "Leave_dates" with "No Agregation" (Please note that "no Groupping" action was required)
- Some unnnessesary columns were also deletedAs a final result I've got the desired table:
Here is the ultimate M code:
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Index_Group", each [Index]/2), #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Added Custom", {{"Index_Group", type text}}, "en-US"), "Index_Group", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Index_Group.1", "Index_Group.2"}), #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Index_Group.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns1",{{"Index_Group.1", Int64.Type}}), #"Removed Columns2" = Table.RemoveColumns(#"Changed Type4",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Start_End]), "Start_End", "Leave_Date"), #"Renamed Columns3" = Table.RenameColumns(#"Pivoted Column",{{"Index_Group.1", "Group_Index"}}) in #"Renamed Columns3"Thanks a lot for the hints. They have reallly helped me out.
Hi George1973
According to your data, an ID may have multiple pairs of Start/End dates, so only using Pivot doesn't work well for them. You need to group them first, then pivot data in each group.
Below are my transformation steps:
1. Add an Index column starting from 0;
2. Divide Index column by 2 and return the integer of the result;
3. Group by the first 6 columns and Index column, select All Rows for the new column GroupData;
4. Add a custom step to transform the new GroupData column. Pivot it in this step;
5. Expand the transformed GroupData column and select Start and End to expand.
This is the complete M code. You can create a blank query and paste codes into its Advanced Editor to see the result.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSAWNjEyCRCAJAuqICSGQDcXBJYlEJkLbUN9I3MjAyUorVIaTFNS8FrMEYocHQEChiaAokLED6kkAASFdW4rOFkCaK7DEh3R5TZC2WQCGQDpDe5ORkIFlVhWaJmb4hkTogNhCvHmGDEYk2kO4HcxJtsICqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, FMG = _t, ID = _t, Dept = _t, Name = _t, Leave_Type = _t, Start_End = _t, Leave_Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"#", Int64.Type}, {"FMG", Int64.Type}, {"ID", Int64.Type}, {"Dept", type text}, {"Name", type text}, {"Leave_Type", type text}, {"Start_End", type text}, {"Leave_Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"#", "FMG", "ID", "Dept", "Name", "Leave_Type", "Index"}, {{"GroupData", each _, type table [#"#"=nullable number, FMG=nullable number, ID=nullable number, Dept=nullable text, Name=nullable text, Leave_Type=nullable text, Start_End=nullable text, Leave_Date=nullable date, Index=number]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows", {{"GroupData", each Table.Pivot(_, {"Start", "End"}, "Start_End", "Leave_Date")}}),
#"Expanded GroupData" = Table.ExpandTableColumn(Custom1, "GroupData", {"Start", "End"}, {"Start", "End"})
in
#"Expanded GroupData"
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi v-jingzhang ,
Thanks a lot for the reply.
Very interesting approach. I have never used group option before.
Now, what I got:
1. I have implemented two steps you've advised:2. Let's proceed with the groupping:
First 6 columns + Index column (not the cloned integered one)
end after expantion and all of this I'm getting these results:
Should I have received such kind of picture? Well, actualy I could accapt it as a solution, however, idially there won't be any "Null" values in Start and End columns, all values should be in the same rows.
- What role does "Integer-Divided Column" play?
- You have not mentioned about "Leave Date" column, where the final results are stored. What I have to do with it? That's why I have manually decided some steps beside your advices.Here is my M Code. As you can see, there are some operations before indexing and groupping.
let Source = Excel.Workbook(File.Contents("C:\Users\jokhadze\Desktop\HR_Leaves-List_2022.xlsx"), null, true), TBL_Busness_Trips_Table = Source{[Item="TBL_Busness_Trips",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(TBL_Busness_Trips_Table,{{"#", Int64.Type}, {"FMG", Int64.Type}, {"ID", Int64.Type}, {"განყოფილება", type text}, {"სახელი გვარი", type text}, {"წასვლის თარიღი#(lf)1", type date}, {"მოსვლის თარიღი#(lf)1", type date}, {"წასვლის თარიღი#(lf)2", type date}, {"მოსვლის თარიღი#(lf)2", type date}, {"წასვლის თარიღი#(lf)3", type date}, {"მოსვლის თარიღი#(lf)3", type date}, {"წასვლის თარიღი#(lf)4", type date}, {"მოსვლის თარიღი#(lf)4", type date}, {"წასვლის თარიღი#(lf)5", type date}, {"მოსვლის თარიღი#(lf)5", type date}, {"წასვლის თარიღი#(lf)6", type date}, {"მოსვლის თარიღი#(lf)6", type date}, {"წასვლის თარიღი#(lf)7", type date}, {"მოსვლის თარიღი#(lf)7", type date}, {"წასვლის თარიღი#(lf)8", type date}, {"მოსვლის თარიღი#(lf)8", type date}, {"წასვლის თარიღი#(lf)9", type date}, {"მოსვლის თარიღი#(lf)9", type date}, {"წასვლის თარიღი#(lf)10", type date}, {"მოსვლის თარიღი#(lf)10", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"წასვლის თარიღი#(lf)1", "B_Start1"}, {"მოსვლის თარიღი#(lf)1", "B_End1"}, {"წასვლის თარიღი#(lf)2", "B_Start2"}, {"მოსვლის თარიღი#(lf)2", "B_End2"}, {"წასვლის თარიღი#(lf)3", "B_Start3"}, {"მოსვლის თარიღი#(lf)3", "B_End3"}, {"წასვლის თარიღი#(lf)4", "B_Start4"}, {"მოსვლის თარიღი#(lf)4", "B_End4"}, {"წასვლის თარიღი#(lf)5", "B_Start5"}, {"მოსვლის თარიღი#(lf)5", "B_End5"}, {"წასვლის თარიღი#(lf)6", "B_Start6"}, {"მოსვლის თარიღი#(lf)6", "B_End6"}, {"წასვლის თარიღი#(lf)7", "B_Start7"}, {"მოსვლის თარიღი#(lf)7", "B_End7"}, {"წასვლის თარიღი#(lf)8", "B_Start8"}, {"მოსვლის თარიღი#(lf)8", "B_End8"}, {"წასვლის თარიღი#(lf)9", "B_Start9"}, {"მოსვლის თარიღი#(lf)9", "B_End9"}, {"წასვლის თარიღი#(lf)10", "B_Start10"}, {"მოსვლის თარიღი#(lf)10", "B_End10"}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"#", "FMG", "ID", "განყოფილება", "სახელი გვარი"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Start1","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Start2","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Start3","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Start4","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Start4","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Start5","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Start6","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Start7","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Start8","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Start9","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Start10","Start",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","End1","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","End2","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","End3","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","End4","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","End5","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","End6","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","End7","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","End8","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","End9","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","End10","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","End0","End",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","Start0","Start",Replacer.ReplaceText,{"Attribute"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value22", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Leave_Type"}, {"Attribute.2", "Start_End"}, {"Value", "Leave_Date"}}), #"Replaced Value23" = Table.ReplaceValue(#"Renamed Columns1","B","მ",Replacer.ReplaceText,{"Leave_Type"}), #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value23",{{"ID", type text}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"განყოფილება", "Dept"}, {"სახელი გვარი", "Name"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns2", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]/2), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}), #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Integer-Divided Column"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns3", {"#", "FMG", "ID", "Dept", "Name", "Leave_Type", "Leave_Date", "Index"}, {{"GroupData", each _, type table [#"#"=nullable number, FMG=nullable number, ID=nullable text, Dept=nullable text, Name=nullable text, Leave_Type=nullable text, Start_End=nullable text, Leave_Date=date, Index=number, #"Integer-Divided Column"=nullable number]}}), #"Expanded GroupData" = Table.ExpandTableColumn(#"Grouped Rows", "GroupData", {"Start_End"}, {"GroupData.Start_End"}), #"Pivoted Column" = Table.Pivot(#"Expanded GroupData", List.Distinct(#"Expanded GroupData"[GroupData.Start_End]), "GroupData.Start_End", "Leave_Date") in #"Pivoted Column"Thanks in advance,
Dear v-jingzhang ,
I have finally solved the issue, thanks to your main recommendations.
- Index Column added
- Added calculated column: Index column devided by 2
- Calculated column has been splitted extracting the whole number (before " . " delimiter)
- As a result I've got grouped index column where star/end operations are groupped
- First index column has been deleted
- Pivot Start / End Column with values in "Leave_dates" with "No Agregation" (Please note that "no Groupping" action was required)
- Some unnnessesary columns were also deletedAs a final result I've got the desired table:
Here is the ultimate M code:
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Index_Group", each [Index]/2), #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Added Custom", {{"Index_Group", type text}}, "en-US"), "Index_Group", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Index_Group.1", "Index_Group.2"}), #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Index_Group.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns1",{{"Index_Group.1", Int64.Type}}), #"Removed Columns2" = Table.RemoveColumns(#"Changed Type4",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Start_End]), "Start_End", "Leave_Date"), #"Renamed Columns3" = Table.RenameColumns(#"Pivoted Column",{{"Index_Group.1", "Group_Index"}}) in #"Renamed Columns3"Thanks a lot for the hints. They have reallly helped me out.
I'm glad to hear that you have solved the problem by yourself! Thank you for sharing the detailed final solution with the community!