Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Filter table with multiple criteria before expanding

Hi,

I have been looking at all the suggestions on filtering a merged table before expanding it, but most of the answers suggest I can filter by a value which I have to inset, or selecting from a list.

 

I am working on CO2 emissions using UK government fuel factors and I have a list of car numberplates and car sizes per year in a separate table. 

The trouble is, the Year (original merge) fuel type and car size all impact the carbon emissions, so I need to filter for these. Currently I expand the table then run:

= Table.AddColumn(#"Replaced Value", "Correct Factors", each if List.AllTrue ({[NNPACarList.Fuel Type] = [CarFactors.Unit], [NNPACarList.Vehicle Size] = [CarFactors.Car Size]}) = true then true else false)

 

Do I need to do a table.single row select here?

 

The tables I am working with are:

AllCarMileage

DateFY-YearVehicleMileage

 

CarList

VehicleFuel TypeVehicle Size

 

CarFactors

YearCar SizeFuel TypeEmissions Factor

 

I need to multiply the Mileage by the Emissions Factor by taking the year, fuel and vehicle size into account.  how do I do it!?

 

Thanks,

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Here I suggest you to merge three tables as a new and then make your calculation on the new table.

    Merge CarList and CarFactors by [Car Size] and [Fuel Type] > Merge New table1 and AllCarMileage by [Year] and [Vehicle].

    For reference: Merge queries overview

    After expanding all column as you want and then fliter the table to get the final result.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous,

    I have made that table, but because there are so many combinations of fuel type, car size, year that are possible. for each line of data I get 16 possible results that I need to filter. The mileage is updated monthly so for each of our 10 cars I get 10 x 16 results which I need to filter and its not sustainable.

     

    This is the code I am using for the whole query

    let
    Source = Table.Combine({PoolMileage, RangerMileage}),
    #"Added Custom" = Table.AddColumn(Source, "FY-Year", each Date.Year(Date.AddMonths([Date],-3))),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Date", "FY-Year", "Vehicle", "Mileage"}),
    #"Merge Mileage with Details" = Table.NestedJoin(#"Reordered Columns", {"Vehicle"}, NNPACarList, {"Vehicle"}, "NNPACarList", JoinKind.LeftOuter),
    #"Expanded NNPACarList" = Table.ExpandTableColumn(#"Merge Mileage with Details", "NNPACarList", {"Vehicle", "Type", "CO₂ emissions", "Fue Type", "Cylinder capacity", "Vehicle Size"}, {"NNPACarList.Vehicle", "NNPACarList.Type", "NNPACarList.CO₂ emissions", "NNPACarList.Fue Type", "NNPACarList.Cylinder capacity", "NNPACarList.Vehicle Size"}),
    #"Added Business Use Check" = Table.AddColumn(#"Expanded NNPACarList", "Custom", each if [Car Type] = [NNPACarList.Type] then true else false),
    #"Filtered Business Use" = Table.SelectRows(#"Added Business Use Check", each ([Custom] = true)),
    #"Merge Cars with CarFactors" = Table.NestedJoin(#"Filtered Business Use", {"FY-Year"}, CarFactors, {"Year"}, "CarFactors", JoinKind.LeftOuter),
    #"Expanded CarFactors" = Table.ExpandTableColumn(#"Merge Cars with CarFactors", "CarFactors", {"Car Size", "Unit", "Measure", "Attribute", "Emissions"}, {"CarFactors.Car Size", "CarFactors.Unit", "CarFactors.Measure", "CarFactors.Attribute", "CarFactors.Emissions"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded CarFactors"," car","",Replacer.ReplaceText,{"CarFactors.Car Size"}),
    #"Added Filter Criteria" = Table.AddColumn(#"Replaced Value", "Correct Factors", each if List.AllTrue ({[NNPACarList.Fue Type] = [CarFactors.Unit], [NNPACarList.Vehicle Size] = [CarFactors.Car Size]}) = true then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Filter Criteria", each ([Correct Factors] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"NNPACarList.Vehicle", "NNPACarList.Type", "NNPACarList.CO₂ emissions", "NNPACarList.Fue Type", "NNPACarList.Cylinder capacity", "NNPACarList.Vehicle Size", "Custom", "Correct Factors"}),
    #"Added Emissions" = Table.AddColumn(#"Removed Columns", "Emissions", each [Mileage]*[CarFactors.Emissions]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Emissions",{{"Emissions", type number}}),
    #"Added Scope" = Table.AddColumn(#"Changed Type", "Scope", each "1"),
    #"Added Source" = Table.AddColumn(#"Added Scope", "Source", each "Transport"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Source",{{"CarFactors.Unit", "Unit"}, {"CarFactors.Attribute", "Attribute"}, {"CarFactors.Measure", "Consumable"}})
in
    #"Renamed Columns"

     

    and this is what I get.

     

    Thanks