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.
I have table as the one on the left below and would like to transform into the one on the right based on the following condition:
If MTH is 9,10 or 11, sum all the PRICE (SUM all PRICE for MTH 9, SUM all PRICE for MTH 10, SUM all PRICE for MTH 11)
Else, sum the PRICE and divide by 12 (SUM all PRICE for MTH 8 then divide 12, SUM all PRICE for MTH 12 then divide 12)
Really appreciate any help on how to do it in power query.
Solved! Go to Solution.
Hi @Anonymous ,
Here are the pics, might by in reverse order.
Groupby to get your sums, then divide by 12 to get your division, the use conditional col to decide which col to use. Then delete intermediate columns.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
You can do this all in a single aggregation within the Table.Group function:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"ACT/FC", type text}, {"MTH", Int64.Type}, {"TYPE", type text}, {"PRICE", Int64.Type}}),
group = Table.Group(#"Changed Type","MTH",{
"PRICE", each
if List.Contains({9..11},[MTH]{0})
then List.Sum([PRICE])
else List.Sum([PRICE])/12, type number
})
in
group
Hi @Anonymous Try this, paste this into your advanced editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslDSUTJUitWBsIzALEsgyxjOMgGzDA2ATFME0wzCNAQyzRFMCwjTCMi0RDCBGmJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [M = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"M", Int64.Type}, {"Column1", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"M"}, {{"Sum", each List.Sum([Column1]), type nullable number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Divide", each [Sum]/12),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Divide", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [M] = 9 or [M] = 10 or [M] = 11 then [Sum] else if [M] = 8 or [M] = 12 then [Divide] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Sum", "Divide"})
in
#"Removed Columns"
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Will follow up with pic
Proud to be a Super User!
Hi @Anonymous ,
Here are the pics, might by in reverse order.
Groupby to get your sums, then divide by 12 to get your division, the use conditional col to decide which col to use. Then delete intermediate columns.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Proud to be a Super User!
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |