Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
I have the table attached and I want to calculate the average cost for 3 repairs (if available) before and after last repair date.
Note: lastest repair date is in another table than needs to be filled down here.
Solved! Go to Solution.
Read code comments
let
//Read in both tables
// and remove blank rows
// Change "Source" lines to reflect your actual data sources
Source = Excel.CurrentWorkbook(){[Name="Output"]}[Content],
#"Output Table"= Table.TransformColumnTypes(Source,{{"Car", type text}, {"Repair Date", type date}}),
Source2 = Excel.CurrentWorkbook(){[Name="Repairs"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source2,{{"Car", type text}, {"Failure Date", type date}, {"Repair Cost", Int64.Type}}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
//merge with output table and extract Repair Date column for car type
Join = Table.NestedJoin(#"Changed Type","Car",#"Output Table","Car","Join",JoinKind.RightOuter),
#"Expanded Join" = Table.ExpandTableColumn(Join, "Join", {"Repair Date"}, {"Repair Date"}),
//Group by car and compute averages
#"Grouped Rows" = Table.Group(#"Expanded Join", {"Car"}, {
{"Repair Date", each [Repair Date]{0}, type date},
{"avg 3 repairs cost before", (t)=>
List.Average(
List.LastN(
Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost],
3)
), type number},
{"avg 3 repairs cost after", (t)=>
List.Average(
List.FirstN(
Table.SelectRows(t, each [Failure Date] > [Repair Date])[Repair Cost],
3)
), type number}
})
in
#"Grouped Rows"
Read code comments
let
//Read in both tables
// and remove blank rows
// Change "Source" lines to reflect your actual data sources
Source = Excel.CurrentWorkbook(){[Name="Output"]}[Content],
#"Output Table"= Table.TransformColumnTypes(Source,{{"Car", type text}, {"Repair Date", type date}}),
Source2 = Excel.CurrentWorkbook(){[Name="Repairs"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source2,{{"Car", type text}, {"Failure Date", type date}, {"Repair Cost", Int64.Type}}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
//merge with output table and extract Repair Date column for car type
Join = Table.NestedJoin(#"Changed Type","Car",#"Output Table","Car","Join",JoinKind.RightOuter),
#"Expanded Join" = Table.ExpandTableColumn(Join, "Join", {"Repair Date"}, {"Repair Date"}),
//Group by car and compute averages
#"Grouped Rows" = Table.Group(#"Expanded Join", {"Car"}, {
{"Repair Date", each [Repair Date]{0}, type date},
{"avg 3 repairs cost before", (t)=>
List.Average(
List.LastN(
Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost],
3)
), type number},
{"avg 3 repairs cost after", (t)=>
List.Average(
List.FirstN(
Table.SelectRows(t, each [Failure Date] > [Repair Date])[Repair Cost],
3)
), type number}
})
in
#"Grouped Rows"
ronrsnfld
In addition to Repair cost, If more costs are needed (i.e, insured cost, shipment cost as an additional column), how the codes look like?
something like this...
{"avg 3 repairs cost before", (t)=> List.Average( List.LastN( Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost][Insured cost][Shipment cost], 3) ), type number},
You will need a separate aggregation for each item you want a separate column/summary.
What the code will look like?
also, I had an issue when two queries are merged, and then expand the table, the average numbers were changed (Seems multiply by 3)!!
@Anonymous wrote:What the code will look like?
Just repeat each pair of aggregations ("average 3 costs before"; "average 3 costs after") for each column you wish to see that information.
@Anonymous wrote:also, I had an issue when two queries are merged, and then expand the table, the average numbers were changed (Seems multiply by 3)!!
I have no idea what you are referring to. The last step in the code I provided has nothing to be expanded.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
11 |