cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MadisMerk Frequent Visitor
Frequent Visitor

Re: New column based on multiple values

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.

 

 

Super User
Super User

Re: New column based on multiple values

@MadisMerk 

 

Tagging champ @ImkeF for help.

 

May be my formula is sub optimal

Highlighted
Super User
Super User

Re: New column based on multiple values

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"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




MadisMerk Frequent Visitor
Frequent Visitor

Re: New column based on multiple values

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 Smiley Very Happy

Madis

Super User
Super User

Re: New column based on multiple values

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.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 309 members 3,350 guests
Please welcome our newest community members: