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 a table like this imported to power query
Vehicle number | Customer name | Vehicle type | Amount |
550 RLA | Toomas | New | 1 |
885 ASS | Toomas | Used | 1 |
854 PPT | Toomas | New | 1 |
754 TYU | Anne | Used | 1 |
125 ASK | Anne | Used | 1 |
855 ANN | Juss | New | 1 |
999 SVE | Juss | Used | 1 |
I would like to add a custom column so that the column says if the customer only has Used vehicles or not. In this case it would look like this:
Vehicle number | Customer name | Vehicle type | Amount | Only used vehicle customer |
550 RLA | Toomas | New | 1 | No |
885 ASS | Toomas | Used | 1 | No |
854 PPT | Toomas | New | 1 | No |
754 TYU | Anne | Used | 1 | Yes |
125 ASK | Anne | Used | 1 | Yes |
855 ANN | Juss | New | 1 | No |
999 SVE | Juss | Used | 1 | No |
Is this doable in power query? If so then how?
Thanks!
Solved! Go to Solution.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.