Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Power Query - Aggregate and transpose help

Hi    Can you please tell me how I can use power query to transform this table Job_Number VoidPK Cost Type Base Amount IWDEC570 C570Voids LABOUR 33 IWDEC634 C634Voids LABOUR 110 ...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi cottrera ,

     

    I've included the code to do what you want below, but the first question is: why do you want to do this? The original format of your data is the optimum and most efficient structure for reporting. Using expensive resources to move the structure further away from optimum is not generally considered best practice.

     

    The answer to your actual question:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZXBbtswDIbfJedCEClRoo5pFwTFMmxovO1Q9NbLTj30/YGRcmDJk6J4xbDTbxsfSIr8RT8/7x5/fjo8ULS7u53Kj7dfr+/yfNrff/3+JA/O7V7uLlhwXjGRBgOwFechcx5ucEyZY2o47wvGlMOJfBSL8YIdTgGUUmkoDM5QFc9yjmd5fIoRxyUxsWIqtxJTyAcRGQTcyn2+BwryRaVN7JyJ9AESSZ2gMia1ShdylW4J+mU/HZ4e96dz7qSxWMFpNmJqjRirkzNk44iMOzTilhEeJ/R6GpXrBjtOYPUgKtcviVA4U9jpIS4UOXlXaaG4WOE4Bc5YaC2T0gU6T5DkXaXXX2/EJNtILqikjHPm2EPRkaH412YYkmUe4sNgsw9D64I/HXudBAKDVWqxmaa22DsROQNcLSZKeTFRGm6csbURja1iEs3WJtvtfjCx2rQUeV7Ig8lvxwLkOkVu7W2c9zaOF8q/GA+j2cRtj1jdnFvjRrCmOtAYhiSwG8BX3f5/SXDjBhQLry5G29HzwzcdEMtC4AUj3aQqvRYlNOy3oZ5MwFJl/oWpdG9lGVEuUqXHOSi3d7hk1qZr/nZriwwCruyB1sBW2HvjQ2HZZZZdN7CYtFSbf1Eq3WIpmlTNKs4DiOuJJgwGquw4OwRxjbmILlf58hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Job_Number = _t, VoidPK = _t, #"Cost Type" = _t, #"Base Amount" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Job_Number", type text}, {"VoidPK", type text}, {"Cost Type", type text}, {"Base Amount", type number}}),
    pivotCostType = Table.Pivot(chgTypes, List.Distinct(chgTypes[#"Cost Type"]), "Cost Type", "Base Amount", List.Sum),
    addGrandTotal = Table.AddColumn(pivotCostType, "grandTotal", each List.Sum({[LABOUR],[MATERIALS],[SCP]}))
in
    addGrandTotal

     

    Pete

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZXBbtswDIbfJedCEClRoo5pFwTFMmxovO1Q9NbLTj30/YGRcmDJk6J4xbDTbxsfSIr8RT8/7x5/fjo8ULS7u53Kj7dfr+/yfNrff/3+JA/O7V7uLlhwXjGRBgOwFechcx5ucEyZY2o47wvGlMOJfBSL8YIdTgGUUmkoDM5QFc9yjmd5fIoRxyUxsWIqtxJTyAcRGQTcyn2+BwryRaVN7JyJ9AESSZ2gMia1ShdylW4J+mU/HZ4e96dz7qSxWMFpNmJqjRirkzNk44iMOzTilhEeJ/R6GpXrBjtOYPUgKtcviVA4U9jpIS4UOXlXaaG4WOE4Bc5YaC2T0gU6T5DkXaXXX2/EJNtILqikjHPm2EPRkaH412YYkmUe4sNgsw9D64I/HXudBAKDVWqxmaa22DsROQNcLSZKeTFRGm6csbURja1iEs3WJtvtfjCx2rQUeV7Ig8lvxwLkOkVu7W2c9zaOF8q/GA+j2cRtj1jdnFvjRrCmOtAYhiSwG8BX3f5/SXDjBhQLry5G29HzwzcdEMtC4AUj3aQqvRYlNOy3oZ5MwFJl/oWpdG9lGVEuUqXHOSi3d7hk1qZr/nZriwwCruyB1sBW2HvjQ2HZZZZdN7CYtFSbf1Eq3WIpmlTNKs4DiOuJJgwGquw4OwRxjbmILlf58hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Job_Number = _t, VoidPK = _t, #"Cost Type" = _t, #"Base Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job_Number", type text}, {"VoidPK", type text}, {"Cost Type", type text}, {"Base Amount", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job_Number", "VoidPK", "Cost Type"}, {{"Grand Total", each List.Sum([Base Amount]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Job_Number", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "LABOUR#(tab)MATERIALS SCP", each if [Cost Type]<>"LABOUR" then [Grand Total] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cost Type"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Job_Number", "VoidPK", "LABOUR#(tab)MATERIALS SCP", "Grand Total"})
in
    #"Reordered Columns"

 

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
4 years ago

Thank you for your response your code works but I have selected the person who responsed first as a solution😁