Hi Madis,
unfortunately you have to group. I've tried to integrate the code as best as I can:
let
Source = Excel.Workbook(File.Contents("Vehicle Park Data\Trucks\Active\Parkas_N2-3-DB_LT-2019-01-01 Over 16 t_with segments.xlsx"), null, true),
#"Ataskaita-Report_Sheet" = Source{[Item="Ataskaita-Report",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Ataskaita-Report_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company name", type text}, {"Company ID", type any}, {"VAT no.", type text}, {"Financer", type text}, {"County", type text}, {"Region", type text}, {"Address", type text}, {"Town", type text}, {"Telephone no.", type any}, {"E-mail", type text}, {"Make", type text}, {"Model", type text}, {"Sort", type text}, {"Type LT", type text}, {"Type EN", type text}, {"Segment", type text}, {"Power, kW", type text}, {"VIN no.", type text}, {"Plate no.", type text}, {"GVW", type any}, {"GVW cat.", type text}, {"Prod. year", type any}, {"Reg. date LT", type any}, {"Reg. date", type any}, {"Column25", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Company name", "VPCustomer"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([County] <> "Apskritis")),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"Company ID", "VPCustomerRegNr"}, {"VAT no.", "VPCustomerVAT"}, {"Financer", "VPFinancing"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"VPCustomerRegNr", type text}, {"VPCustomerVAT", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"County", "VPCustomerCounty"}, {"Region", "VPCustomerRegion"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Column25", "VPCustomerRegion", "Address", "Town", "Telephone no.", "E-mail", "Sort", "Type LT"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns",{{"Prod. year", "VPProductionYear"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns3",{"GVW cat."}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"GVW", "VPWeight"}, {"VIN no.", "VPVIN"}, {"Plate no.", "VPPlateNo"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns4",{"Power, kW"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns2",{{"Segment", "VPCommonSegment"}, {"Reg. date LT", "VPFirstRegistrationDateLocalCountry"}, {"Type EN", "VPSegmentLocal"}, {"Make", "VPMake"}, {"Model", "VPModel"}, {"Reg. date", "VPFirstRegistrationDate"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns5", "VPCountry", each "Lithuania"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "VPAmount", each 1),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"VPAmount", Int64.Type}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type2", each true),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows2",{{"VPWeight", Int64.Type}, {"VPProductionYear", type text}, {"VPFirstRegistrationDateLocalCountry", type date}, {"VPFirstRegistrationDate", type date}, {"VPCountry", type text}}),
#"Renamed Columns6" = Table.RenameColumns(#"Changed Type3",{{"VPCommonSegment", "VPSegmentCommon"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns6", "VPNewOrUsed", each if [VPFirstRegistrationDateLocalCountry] = [VPFirstRegistrationDate] then "New" else "Used"),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "VPVehicleAge", each Date.Year(DateTime.LocalNow())-Date.Year([VPFirstRegistrationDate])),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"VPNewOrUsed", type text}, {"VPVehicleAge", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type4", {"VPCustomerRegNr"}, {{"All", each _}}),
#"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Only used vehicle number", each if List.Distinct([All][VPNewOrUsed]) = {"Used"} then "Yes" else "No"),
#"Expanded All" = Table.ExpandTableColumn(#"Added Custom4", "All", {"Vehicle number", "Vehicle type", "Amount"}, {"Vehicle number", "Vehicle type", "Amount"})
in
#"Expanded All"
You definitely have to adjust the last step and expand your specific columns there.
