Power Query - Aggregate and transpose help

[cottrera]

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

[BA_Pete · Accepted 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
    addGrandTotal

Pete

[cottrera]

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

[Vijay_A_Verma]

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]

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