Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MadisMerk
Helper I
Helper I

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!

1 ACCEPTED 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

View solution in original post

14 REPLIES 14
v-frfei-msft
Community Support
Community Support

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" )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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!

@MadisMerk 

 

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"

 laststep.png

 

 


Regards
Zubair

Please try my custom visuals

Hi,

 

Really appreciate your help here.

 

Yes, VPNewOrUsed is also a calculated column. I use this formula to calculate it:

 

= Table.AddColumn(#"Renamed Columns6", "VPNewOrUsed", each if [VPFirstRegistrationDateLocalCountry] = [VPFirstRegistrationDate] then "New" else "Used")

 

I copied this from the advanced editor.

 

Does this make any difference?

 

@MadisMerk 

 

Yes it does make a difference.

 

In the formula I gave you I used "Changed Type" step..(This is the step name from applied steps in Query Editor)

 

The Column "VPNewOrUsed" was created after the "Changed Type" step. Thats why you get the error that this Column wasn't found.

 

If you just replace "Changed Type" with the StepName (in which VPNewOrUsed was created), I believe the formula would work


Regards
Zubair

Please try my custom visuals

Hi,

 

I now managed to get it working! Thank´s a lot for your help. But I discovered that when I now try to load this into power BI it has become huge, I waited until the load came to 2 GB and then canceled. I think the table has more than 60 000 rows, any ideas on how to improve this

@MadisMerk 

 

Try with Table.Buffer

Go to Advanced Editor, 

Wrap the step (which is just before the step in which you added this custom column) inside Table.Buffer

 

For example

 

	StepName = Table.Buffer(_______________),


   	 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 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)
                    in
                    #"Added Custom"

Regards
Zubair

Please try my custom visuals

Hi Again,

 

Now I managed to get that table loading fast. But I also run an append query, which appends two tables "VPLithuaniaTrucks" and "VPLithuaniaBus" into one called VPBaltics. It loads these tables but then it freezes and this window is shown for hours:

 

Noload.jpg

 

The Parkas file Is actually the source for the "VPLithuanianTruck" table. And this window stays at 919 KB.

 

 

@MadisMerk 

 

Tagging champ @ImkeF for help.

 

May be my formula is sub optimal


Regards
Zubair

Please try my custom visuals

Yes, Table.SelectRows in a row-expression becomes slow very soon. (see this blogpost for example: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power... )

 

Try this approach instead:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1UAjycVTSUQrJz89NLAYy/FLLgaShUqxOtJKFhamCY3AwsnRocWoKQt7URCEgIASXdnOgdEhkKJDvmJeXiq7Z0AhkuDcOWQtToKyfH5DvVVqMbrClpaVCcJgrQhKhNRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Vehicle number" = _t, #"Customer name" = _t, #"Vehicle type" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vehicle number", type text}, {"Customer name", type text}, {"Vehicle type", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer name"}, {{"All", each _, type table [Vehicle number=text, Customer name=text, Vehicle type=text, Amount=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Only used vehicle number", each if List.Distinct([All][Vehicle type]) = {"Used"} then "Yes" else "No"),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"Vehicle number", "Vehicle type", "Amount"}, {"Vehicle number", "Vehicle type", "Amount"})
in
    #"Expanded All"

 

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

Any way this could be written as a script for the custom column window? Because I have this at the moment, not exactly the one I presented as the example:


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}})
in
    #"Changed Type4"

And my IQ is not big enough to merge this with the one that you have written 😄

Madis

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

Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

Hi,

 

Thanks for the formula. I inserted this into the actual Power BI report like this (column definitions are a bit different):

 

Pic1.jpg

 

And it gives me this error:

 

Expression.Error: The column 'VPNewOrUsed' of the table wasn't found.
Details:
VPNewOrUsed

 

But I have this column available in the table:

 

Pic2.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors