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