Forum Discussion
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 |
| IWDEC641 | C641Voids | LABOUR | 110 |
| IWDEC685 | C685Voids | LABOUR | 44 |
| IWDEC851 | C851Voids | LABOUR | 44 |
| IWDEC851 | C851Voids | LABOUR | 77 |
| IWEL611 | L611Voids | LABOUR | 263.5 |
| IWDEC808 | C808Voids | LABOUR | 110 |
| IWDEC808 | C808Voids | LABOUR | 187 |
| IWEL588 | L588Voids | LABOUR | 263.5 |
| IWDEC561 | C561Voids | LABOUR | 187 |
| IWDEC561 | C561Voids | LABOUR | 187 |
| IWKB156 | B156Voids | LABOUR | 233.75 |
| IWKB156 | B156Voids | LABOUR | 233.75 |
| IWKB254 | B254Voids | LABOUR | 233.75 |
| IWDEC536 | C536Voids | MATERIALS | 11.02 |
| IWDEC590 | C590Voids | LABOUR | 77 |
| IWDEC815 | C815Voids | LABOUR | 187 |
| IWDEC815 | C815Voids | LABOUR | 110 |
| IWGT244 | T244Voids | LABOUR | 44 |
| IWGT106 | T106Voids | LABOUR | 33 |
| IWGT126 | T126Voids | LABOUR | 22 |
| IWGT53 | GT53Voids | LABOUR | 27.5 |
| IWGT68 | GT68Voids | LABOUR | 99 |
| IWST19 | ST19Voids | MATERIALS | 4.61 |
| IWST19 | ST19Voids | MATERIALS | 48.61 |
| IWGT67 | GT67Voids | MATERIALS | 235.57 |
| IWKB254 | B254Voids | LABOUR | 233.75 |
| IWKB254 | B254Voids | LABOUR | 110 |
| IWKB160 | B160Voids | LABOUR | 233.75 |
| IWKB160 | B160Voids | LABOUR | 151.25 |
| IWKB202 | B202Voids | MATERIALS | 53.18 |
| IWDEC659 | C659Voids | LABOUR | 44 |
| IWDEC536 | C536Voids | MATERIALS | 22.08 |
| IWDEC550 | C550Voids | MATERIALS | 46.73 |
| IWDEC578 | C578Voids | LABOUR | 99 |
| IWDEC578 | C578Voids | LABOUR | 99 |
| IWDEC616 | C616Voids | LABOUR | 110 |
| IWDEC642 | C642Voids | LABOUR | 187 |
| IWKB160 | B160Voids | LABOUR | 233.75 |
| IWKB160 | B160Voids | LABOUR | 82.5 |
| IWKB160 | B160Voids | LABOUR | 233.75 |
| IWKB160 | B160Voids | LABOUR | 27.5 |
| IWKB202 | B202Voids | MATERIALS | 210.87 |
| IWKB202 | B202Voids | MATERIALS | 190.83 |
| IWKB202 | B202Voids | LABOUR | 233.75 |
| IWKB202 | B202Voids | LABOUR | 233.75 |
| IWKB202 | B202Voids | LABOUR | 233.75 |
| IWKB202 | B202Voids | LABOUR | 137.5 |
| IWKB202 | B202Voids | LABOUR | 41.25 |
| IWKB160 | B160Voids | SCP | 888.68 |
| IWKB55 | KB55Voids | MATERIALS | 92.84 |
| IWKB55 | KB55Voids | MATERIALS | 45.62 |
| IWKB261 | B261Voids | MATERIALS | 57 |
| IWKB60 | KB60Voids | MATERIALS | 31.18 |
| IWKB254 | B254Voids | LABOUR | 82.5 |
| IWKB156 | B156Voids | MATERIALS | 21 |
| IWKB254 | B254Voids | MATERIALS | 120.11 |
| IWKB254 | B254Voids | MATERIALS | 44.46 |
| IWKB283 | B283Voids | MATERIALS | 110.5 |
| IWKB44 | KB44Voids | MATERIALS | 257.98 |
| IWKB57 | KB57Voids | SCP | 926.16 |
| IWKB222 | B222Voids | SCP | 3723.46 |
Into this.
| Job_Number | VoidPK | LABOUR | MATERIALS | SCP | Grand Total |
| IWDEC536 | C536Voids | 33.1 | 33.1 | ||
| IWDEC550 | C550Voids | 46.73 | 46.73 | ||
| IWDEC561 | C561Voids | 374 | 374 | ||
| IWDEC570 | C570Voids | 33 | 33 | ||
| IWDEC578 | C578Voids | 198 | 198 | ||
| IWDEC590 | C590Voids | 77 | 77 | ||
| IWDEC616 | C616Voids | 110 | 110 | ||
| IWDEC634 | C634Voids | 110 | 110 | ||
| IWDEC641 | C641Voids | 110 | 110 | ||
| IWDEC642 | C642Voids | 187 | 187 | ||
| IWDEC659 | C659Voids | 44 | 44 | ||
| IWDEC685 | C685Voids | 44 | 44 | ||
| IWDEC808 | C808Voids | 297 | 297 | ||
| IWDEC815 | C815Voids | 297 | 297 | ||
| IWDEC851 | C851Voids | 121 | 121 | ||
| IWEL588 | L588Voids | 263.5 | 263.5 | ||
| IWEL611 | L611Voids | 263.5 | 263.5 | ||
| IWGT106 | T106Voids | 33 | 33 | ||
| IWGT126 | T126Voids | 22 | 22 | ||
| IWGT244 | T244Voids | 44 | 44 | ||
| IWGT53 | GT53Voids | 27.5 | 27.5 | ||
| IWGT67 | GT67Voids | 235.57 | 235.57 | ||
| IWGT68 | GT68Voids | 99 | 99 | ||
| IWKB156 | B156Voids | 467.5 | 21 | 488.5 | |
| IWKB160 | B160Voids | 962.5 | 888.68 | 1851.18 | |
| IWKB202 | B202Voids | 880 | 454.88 | 1334.88 | |
| IWKB222 | B222Voids | 3723.46 | 3723.46 | ||
| IWKB254 | B254Voids | 660 | 164.57 | 824.57 | |
| IWKB261 | B261Voids | 57 | 57 | ||
| IWKB283 | B283Voids | 110.5 | 110.5 | ||
| IWKB44 | KB44Voids | 257.98 | 257.98 | ||
| IWKB55 | KB55Voids | 138.46 | 138.46 | ||
| IWKB57 | KB57Voids | 926.16 | 926.16 | ||
| IWKB60 | KB60Voids | 31.18 | 31.18 | ||
| IWST19 | ST19Voids | 53.22 | 53.22 |
I basically need to aggregate the base amount and transpose the cost type.
thank you Richard
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 addGrandTotalPete
4 Replies
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"Thank you for your response your code works but I have selected the person who responsed first as a solution😁
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 addGrandTotalPete
Hi BA_Peter thank you for your response . The code works perfectly. In response as to why I am doing this. Its for a colleage and I will menstion your best practice comments
RIchard
