Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
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😁
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.