none
Power Query for Row level substraction in same column based on multiple condition RRS feed

  • Question

  • Hi All,

    I am automating 1 of my report using power query but stuck in one logic and unable to move further. I need your expert advice and suggestion on below point :-

    I have to add a new column name " Adjusted Utilization" logic for the same is that :-

    1) Attribute column needs to change to change as 1st date of every month

    2) it needs to check for combination of Signum & Domain and first check for 1 occurrence with this and in "Adjusted Utilization column" should be like "Zero" - value mentioned in Utilization column.

    3) After completing point no. 2 task , query needs to subtract(above cell -Below cell) utilization with same combination.

    Below is the existing query with I already done partially and need to add new steps for Points mentioned in 1, 2, 3.

    I am also pasting the simple excel formula for desired output :-

    SIGNUM DOMAIN NAME Attribute Utilization Adjusted Utilization
    A0001 Supply A Feb-19 1 =0-E2
    A0001 Supply A Mar-19 1 =E2-E3
    A0001 Supply A Apr-19 0.5 =E3-E4
    A0001 Supply A May-19 0 =E4-E5
    A0001 Admin A Jun-19 0.5 =0-E6
    A0001 Admin A Jul-19 0.25 =E6-E7
    A0002 Admin A Aug-19 0 =E7-E8
    A0002 Admin B Jan-19 0.5 =0-E9
    A0002 Admin B Feb-19 0.5 =E9-E10
    A0002 Admin B Mar-19 0 =E10-E11
    A0002 Accounts B Apr-19 0.5 =E11-E12
    A0002 Accounts B May-19 0.5 =E12-E13
    A0002 Accounts B Jun-19 0.1 =E13-E14
    A0002 Accounts B Jul-19 0.1 =E14-E15
    A0002 Accounts B Aug-19 0 =E15-E16

    let
        Source = Excel.CurrentWorkbook(){[Name="ASSIGNMENTS"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"SIGNUM", type text}, {"DOMAIN", type text}, {"SUB-DOMAIN", type text}, {"CPM RESPONSIBLE", type text}, {"NAME", type text}, {"RESOURCE #(lf)STATUS", type text}, {"ASSIGNMENT STATUS", type text}, {"ORIGINAL START DATE", type datetime}, {"ASSIGNMENT START DATE", type datetime}, {"END DATE", type datetime}, {"TRAVEL PROFILE", type text}, {"HOME BASE", type any}, {"ASSIGNMENT #(lf)LOCATION", type text}, {"ONSITE TMO OFFICE ADDRESS", type text}, {"PROJECT ROLE", type text}, {"HOURLY RATE", type number}, {"Comments", type text}, {"Jan-14", Int64.Type}, {"Feb-14", Int64.Type}, {"Mar-14", type number}, {"Apr-14", type number}, {"May-14", type number}, {"Jun-14", type number}, {"Jul-14", type number}, {"Aug-14", type number}, {"Sep-14", type number}, {"Oct-14", type number}, {"Nov-14", type number}, {"Dec-14", type number}, {"Jan-15", type number}, {"Feb-15", type number}, {"Mar-15", type number}, {"Apr-15", type number}, {"May-15", type number}, {"Jun-15", type number}, {"Jul-15", type number}, {"Aug-15", type number}, {"Sep-15", type number}, {"Oct-15", type number}, {"Nov-15", type number}, {"Dec-15", type number}, {"Jan-16", type number}, {"Feb-16", type number}, {"Mar-16", type number}, {"Apr-16", type number}, {"May-16", type number}, {"Jun-16", type number}, {"Jul-16", type number}, {"Aug-16", type number}, {"Sep-16", type number}, {"Oct-16", type number}, {"Nov-16", type number}, {"Dec-16", type number}, {"Jan-17", type number}, {"Feb-17", type number}, {"Mar-17", type number}, {"Apr-17", type number}, {"May-17", type number}, {"Jun-17", type number}, {"Jul-17", type number}, {"Aug-17", type number}, {"Sep-17", type number}, {"Oct-17", type number}, {"Nov-17", type number}, {"Dec-17", type number}, {"Jan-18", type number}, {"Feb-18", type number}, {"Mar-18", type number}, {"Apr-18", type number}, {"May-18", type number}, {"Jun-18", type number}, {"Jul-18", type number}, {"Aug-18", type number}, {"Sep-18", type number}, {"Oct-18", type number}, {"Nov-18", type number}, {"Dec-18", type number}, {"Jan-19", type number}, {"Feb-19", type number}, {"Mar-19", type number}, {"Apr-19", type number}, {"May-19", type number}, {"Jun-19", type number}, {"Jul-19", type number}, {"Aug-19", type number}, {"Sep-19", type number}, {"Oct-19", type number}, {"Nov-19", type number}, {"Dec-19", type number}, {"Jan-20", Int64.Type}, {"Feb-20", type any}, {"Mar-20", type any}, {"Apr-20", type any}, {"May-20", type any}, {"Jun-20", type any}, {"Jul-20", type any}, {"Aug-20", type any}, {"Sep-20", type any}, {"Oct-20", type any}, {"Nov-20", type any}, {"Dec-20", type any}, {"FIRST NAME", type text}, {"LAST NAME", type text}, {"E-MAIL", type text}, {"RELATION", type any}, {"COUNTRY", type text}, {"RELATION2", type text}, {"LINE MANAGER", type text}, {"DEPARTMENT", type any}, {"Department2", type text}, {"COMPANY", type any}, {"TITLE", type text}, {"PERSONNEL NUMBER", type any}, {"Contracting Agency Name", type text}, {"UPDATE_RES_INFO", Int64.Type}, {"Date of Onboarding/Movement in RP", type datetime}, {"Onboarding Utilization", Int64.Type}, {"Date of Off-boarding in RP", type datetime}, {"Scheduled Off-boarding from FG", type datetime}, {"Off-boarding Utilization", type number}, {"TOTAL TRAVEL COST-LAST 12 WEEKS*", type number}, {"BAD START DATE", type any}, {"BAD END DATE", type any}, {"MOBILE", type any}, {"COST CENTRE", Int64.Type}, {"PDU Utilization", type any}, {"Missing Cells Utilization", type text}, {"Current Month Utilization", type number}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"SIGNUM", "DOMAIN", "NAME", "ASSIGNMENT START DATE", "END DATE", "Jan-18", "Feb-18", "Mar-18", "Apr-18", "May-18", "Jun-18", "Jul-18", "Aug-18", "Sep-18", "Oct-18", "Nov-18", "Dec-18", "Jan-19", "Feb-19", "Mar-19", "Apr-19", "May-19", "Jun-19", "Jul-19", "Aug-19", "Sep-19", "Oct-19", "Nov-19", "Dec-19", "Jan-20", "Feb-20", "Mar-20", "Apr-20", "May-20", "Jun-20", "Jul-20", "Aug-20", "Sep-20", "Oct-20", "Nov-20", "Dec-20"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"SIGNUM", "DOMAIN", "NAME", "ASSIGNMENT START DATE", "END DATE"}, "Attribute", "Value"),
        #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Utilization"}}),
        #"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"SIGNUM", "DOMAIN", "Attribute","Utilization"}),
        #"Generate Date" = Table.AddColumn(#"Removed Duplicates", "Custom", each { Number.From([ASSIGNMENT START DATE])..Number.From([END DATE]) }),
        #"Expanded Custom" = Table.ExpandListColumn(#"Generate Date", "Custom"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ASSIGNMENT START DATE", "END DATE", "Custom"}),
        #"Sorted Rows1" = Table.Sort(#"Removed Columns",{{"DOMAIN", Order.Ascending},{"Utilization", Order.Ascending}}),
        #"Removed Duplicates1" = Table.Distinct(#"Sorted Rows1", {"SIGNUM", "DOMAIN", "Attribute"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates1", each ([NAME] = "A"))
    in
        #"Filtered Rows"

     


    Rajender

    Tuesday, October 29, 2019 7:31 PM

Answers

  • Hello Negi1984

    I've found a solution that involves Grouping the data and then do a multiple TransformColumns.

    However, I don't understand your query because it doesn't lead to the structure you showed above.

    You find my code start from Variable SortRow2

    let
        Source = Excel.CurrentWorkbook(){[Name="ASSIGNMENTS"]}[Content],
        RemovedDuplicates = Table.Distinct(Source, {"SIGNUM", "DOMAIN", "Attribute","Utilization"}),
        SortRows = Table.Sort(RemovedDuplicates,{{"DOMAIN", Order.Ascending},{"Utilization", Order.Ascending}}),
        RemoveDuplicate = Table.Distinct(SortRows, {"SIGNUM", "DOMAIN", "Attribute"}),
        SortRows2 = Table.Sort(RemoveDuplicate,{{"NAME", Order.Ascending}, {"Attribute", Order.Ascending}}),
        AddIndex = Table.AddIndexColumn(SortRows2, "OverallIndex", 1, 1),
        GroupNAME = Table.Group(
            AddIndex , 
            { "NAME"}, 
            {{"All", 
                each _, 
                type table [SIGNUM=text, DOMAIN=text, NAME=text, Attribute=datetime, Utilization=number]}}),
        GetUtilizationAbove = 
            (Table, Index) => 
                Table.SelectRows(Table, each [Index] = Index-1)[Utilization]{0},
        TransformColumnSortSignumNameAttribute= Table.TransformColumns(
            GroupNAME, 
            {"All", each Table.Sort(
                _, 
                {{"SIGNUM",Order.Ascending}, {"NAME",Order.Ascending}, {"Attribute",Order.Ascending}})}),
        TransformColumnAddIndex = Table.TransformColumns(
            TransformColumnSortSignumNameAttribute, 
            {"All", each Table.AddIndexColumn(_, "Index", 1, 1)}),
        TransformColumnAddColumn = Table.TransformColumns(
            TransformColumnAddIndex , 
            {"All", (x) => Table.AddColumn(
                x,
                "Adjusted Utilization", 
                (y)=> if y[Index]= 1 then 
                    0 - y[Utilization] 
                    else 
                    GetUtilizationAbove (x, y[Index]) - y[Utilization])}),
        DeleteColumns = Table.RemoveColumns(
            TransformColumnAddColumn,
            { "NAME"}),
        ExpandAll = Table.ExpandTableColumn(
            DeleteColumns, 
            "All", 
            {"SIGNUM", "DOMAIN", "NAME", "Attribute", "Utilization", "OverallIndex", "Index", "Adjusted Utilization"}, {"SIGNUM", "DOMAIN", "NAME", "Attribute", "Utilization", "OverallIndex", "Index", "Adjusted Utilization"}),
        RowSort = Table.Sort(
            ExpandAll,
            {{"SIGNUM", Order.Ascending}, {"NAME", Order.Ascending}, {"Attribute", Order.Ascending}})
    in
        RowSort


    Query it

    Saturday, November 23, 2019 10:27 AM

All replies

  • Hi Rajender,

    Were you able to figure out a solution for this?

    Ehren

    Tuesday, November 19, 2019 6:09 PM
    Owner
  • Hi Ehren,

    Not yet. I am still awaiting for the solution.


    Rajender

    Wednesday, November 20, 2019 7:35 PM
  • Hello Negi1984

    I've found a solution that involves Grouping the data and then do a multiple TransformColumns.

    However, I don't understand your query because it doesn't lead to the structure you showed above.

    You find my code start from Variable SortRow2

    let
        Source = Excel.CurrentWorkbook(){[Name="ASSIGNMENTS"]}[Content],
        RemovedDuplicates = Table.Distinct(Source, {"SIGNUM", "DOMAIN", "Attribute","Utilization"}),
        SortRows = Table.Sort(RemovedDuplicates,{{"DOMAIN", Order.Ascending},{"Utilization", Order.Ascending}}),
        RemoveDuplicate = Table.Distinct(SortRows, {"SIGNUM", "DOMAIN", "Attribute"}),
        SortRows2 = Table.Sort(RemoveDuplicate,{{"NAME", Order.Ascending}, {"Attribute", Order.Ascending}}),
        AddIndex = Table.AddIndexColumn(SortRows2, "OverallIndex", 1, 1),
        GroupNAME = Table.Group(
            AddIndex , 
            { "NAME"}, 
            {{"All", 
                each _, 
                type table [SIGNUM=text, DOMAIN=text, NAME=text, Attribute=datetime, Utilization=number]}}),
        GetUtilizationAbove = 
            (Table, Index) => 
                Table.SelectRows(Table, each [Index] = Index-1)[Utilization]{0},
        TransformColumnSortSignumNameAttribute= Table.TransformColumns(
            GroupNAME, 
            {"All", each Table.Sort(
                _, 
                {{"SIGNUM",Order.Ascending}, {"NAME",Order.Ascending}, {"Attribute",Order.Ascending}})}),
        TransformColumnAddIndex = Table.TransformColumns(
            TransformColumnSortSignumNameAttribute, 
            {"All", each Table.AddIndexColumn(_, "Index", 1, 1)}),
        TransformColumnAddColumn = Table.TransformColumns(
            TransformColumnAddIndex , 
            {"All", (x) => Table.AddColumn(
                x,
                "Adjusted Utilization", 
                (y)=> if y[Index]= 1 then 
                    0 - y[Utilization] 
                    else 
                    GetUtilizationAbove (x, y[Index]) - y[Utilization])}),
        DeleteColumns = Table.RemoveColumns(
            TransformColumnAddColumn,
            { "NAME"}),
        ExpandAll = Table.ExpandTableColumn(
            DeleteColumns, 
            "All", 
            {"SIGNUM", "DOMAIN", "NAME", "Attribute", "Utilization", "OverallIndex", "Index", "Adjusted Utilization"}, {"SIGNUM", "DOMAIN", "NAME", "Attribute", "Utilization", "OverallIndex", "Index", "Adjusted Utilization"}),
        RowSort = Table.Sort(
            ExpandAll,
            {{"SIGNUM", Order.Ascending}, {"NAME", Order.Ascending}, {"Attribute", Order.Ascending}})
    in
        RowSort


    Query it

    Saturday, November 23, 2019 10:27 AM