optiimize load data

[Anonymous]

I use the following scipt in powerquey in oder to load data, it takes too much time, how to optimmize it

let
Source = Csv.Document(File.Contents("C:\Users\Jawaher\OneDrive\powerbi-export\powerbi-proposals.txt"),[Delimiter=","]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
// #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([rejected_by_internal_filter] = "false")),
#"Changed Type" = Table.TransformColumnTypes( #"Promoted Headers",{{"id", type text}, {"application_id", type text}, {"bank", type text}, {"amount_approved", Int64.Type}, {"rate", Int64.Type}, {"rate_effective", Int64.Type}, {"monthly_cost", Int64.Type}, {"years_repayment", Int64.Type}, {"consolidation_required", type text}, {"rejected_by_internal_filter", type logical}, {"created_time", type datetime}, {"created_date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"application_id"}, applications, {"id"}, "applications", JoinKind.Inner),
#"Expanded applications" = Table.ExpandTableColumn(#"Merged Queries", "applications", {"amount", "amount_approved", "created_date", "accepted_bank", "accepted_amount", "disbursed_date", "disbursed_bank", "disbursed_amount", "utm_clientid", "acceptance_amount", "acceptance_monthly_cost"}, {"applications.amount", "applications.amount_approved", "applications.created_date", "applications.accepted_bank", "applications.accepted_amount", "applications.disbursed_date", "applications.disbursed_bank", "applications.disbursed_amount", "applications.utm_clientid", "applications.acceptance_amount", "applications.acceptance_monthly_cost"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded applications", each ([rejected_by_internal_filter] = false)),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"bank", "applications.created_date", "applications.accepted_bank", "application_id", "applications.utm_clientid"}, {{"amount", each List.Max([applications.amount]), type nullable number}, {"amountapproved", each List.Max([applications.amount_approved]), type nullable number}, {"amountaccepted", each List.Max([applications.accepted_amount]), type nullable number}, {"disbursed_amount", each List.Max([applications.disbursed_amount]), type nullable number}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"applications.created_date", "created_date"}, {"amountaccepted", "accepted_amount"}}),
#"Grouped Rows1" = Table.Group(#"Renamed Columns", {"bank", "created_date", "applications.accepted_bank", "applications.utm_clientid"}, {{"amount", each List.Sum([amount]), type nullable number}, {"amountapproved", each List.Sum([amountapproved]), type nullable number}, {"accepted_amount", each List.Sum([accepted_amount]), type nullable number}, {"disbursed_amount", each List.Sum([disbursed_amount]), type nullable number}}),
#"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows1",{{"amountapproved", "amount_approved"}, {"applications.accepted_bank", "accepted_bank"}, {"applications.utm_clientid", "utm_clientid"}})
in
#"Renamed Columns1"
// proposals_Sheet

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

It's always recommended to filter your data in the early stages of your query or as early as possible. Some connectors will take advantage of your filters through query folding, as described in Power Query query folding. 

And please refer to this document about Optimize Power Query when expanding table columns

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.