Pivot Column NonAgregate Error

[Syndicate_Admin]

Hi All,

I have a following table:

Where I want to pivot the column "Start_End" with the "Leave_Date" column values for every "ID".. Like given below for instance (ID = 82):

 When I use "pivot column" option in the Power Query, it works only for agragate functions like Count, or Minimum, mAximum.. BUt I just want to get nonagragatesd values. like given above. In this case I'm getting errors:

(In case of Count(all))

In case of Maximum..

In case of "Do not Agragate"..

Please advise which option (Feature) I need to use to get the desired result?

Thanks in advance,

[Syndicate_Admin]

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.

[Syndicate_Admin]

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,

[Syndicate_Admin · Accepted solution]

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 deleted

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

[Syndicate_Admin]

I'm glad to hear that you have solved the problem by yourself! Thank you for sharing the detailed final solution with the community!