Forum Discussion
New column based on multiple values
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!
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.
14 Replies
Hi MadisMerk
We meet again
Try this custom column.
#"Changed Type" is just the previous step name in the Query Editor
let mytypes=Table.SelectRows(#"Changed Type",(x)=>[Customer name]=(x)[Customer name])[Vehicle type] in List.Contains(mytypes,"Used") and List.Contains(mytypes,"New")=false
Hi,
Thanks for the formula. I inserted this into the actual Power BI report like this (column definitions are a bit different):
And it gives me this error:
Expression.Error: The column 'VPNewOrUsed' of the table wasn't found.
Details:
VPNewOrUsedBut I have this column available in the table:
Hi MadisMerk ,
To create a calculated column should be easy.
Column = VAR a = CALCULATETABLE ( VALUES ( 'Table'[Vehicle type] ), ALLEXCEPT ( 'Table', 'Table'[Customer name] ) ) RETURN IF ( "New" IN a, "NO", "YES" )Hi,
This is a nice solution, but as Power BI does not allow you to use a measure in "Page level filters" then I cannot use it.
That is why I was asking for this solution in the query editor.
Cheers!
is VPNewOrUsed a computed column itself???
In the formula, replace the following with the last step's name in the Query Editor (i.e the step name before you add the Custom formula)
#"Changed Type"
