Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
4 years ago
Solved

Pivot Column NonAgregate Error

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 ...
Need Help
  • George1973's avatar
    George1973
    4 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

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.