How to speed up "Connecting to data source" - Help!

[Darkzler]

Hello,

This is my Power Query, there is one query with all the main data that I later on merge with 3 additional queries. I complement the main data with volumes, categories and types. However, I get stuck on "Connecting to data source", the load time is about 60 minutes.

The file sizes are:
Data IG3: 19 files that are in total 90,0MB.
Data TR2: 5 files that are in total 68,1MB.

All the data is located on a network folder, do you have any tips on how I can speed this up? Should I move it to my local PC and just put the output on the network drive? I've also checked "Fast Data Load" on the queries.

Best regards,
Dark

let
Source = Folder.Files("XXXXXX\Volymrapport V.2\Data TR2"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Consignment", type text}, {"FH_eksped_YYYY_MM_DD", Int64.Type}, {"G Weight", type number}, {"Payweight", type number}, {"PW Interval", type text}, {"PW interval ICS", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"FH_eksped_YYYY_MM_DD", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([FH_eksped_YYYY_MM_DD] <> null)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Consignment", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Consignment.1", "Consignment.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Consignment.1", type text}, {"Consignment.2", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Consignment.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Consignment.1", "CON"}, {"FH_eksped_YYYY_MM_DD", "Datum"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"CON"}, #"Data IG3", {"Shipment"}, "Data IG3", JoinKind.LeftOuter),
#"Expanded Data IG3" = Table.ExpandTableColumn(#"Merged Queries", "Data IG3", {"Colli", "Type", "CBM", "LDM", "Payweight", "Hazard", "Local Customer Account", "Local Customer"}, {"Data IG3.Colli", "Data IG3.Type", "Data IG3.CBM", "Data IG3.LDM", "Data IG3.Payweight", "Data IG3.Hazard", "Data IG3.Local Customer Account", "Data IG3.Local Customer"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Data IG3", {"CON"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Data IG3.Local Customer Account", type text}}),
#"Merged Queries1" = Table.NestedJoin(#"Changed Type3", {"Data IG3.Local Customer Account"}, Kategorier, {"Column1"}, "Kategorier", JoinKind.LeftOuter),
#"Expanded Kategorier" = Table.ExpandTableColumn(#"Merged Queries1", "Kategorier", {"Line of Business", "Line of Business (Parent Account)"}, {"Kategorier.Line of Business", "Kategorier.Line of Business (Parent Account)"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Kategorier", {"Data IG3.Type"}, Typer, {"Kollityp"}, "Typer", JoinKind.LeftOuter),
#"Expanded Typer" = Table.ExpandTableColumn(#"Merged Queries2", "Typer", {"Typ"}, {"Typer.Typ"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Typer",null,"Not approved",Replacer.ReplaceValue,{"Typer.Typ"})
in
#"Replaced Value"

 

[GraH]

You have multiple merged queries step.
It might help too:
- add a key to those queries (Table.AddKey)
- buffer the queries

 

[Darkzler]

You have multiple merged queries step.
It might help too:
- add a key to those queries (Table.AddKey)
- buffer the queries

Sorry but could you please elaborate on this? Yeah, I got multiple queries but what does the "Add a key" do to this? And also, what is buffer the queries and how do I do that?

 

[GraH]

Please try to update your codes like this

Let
...
AddKey = Table.AddKey(PrevStep, { "Kollitup"}, true)
In 
AddKey

Let
bTyper= Table.Buffer(Typer),
MergeTyper= Table.NestedJoin(#"Expanded Kategorier", {"Data IG3.Type"}, bTyper, {"Kollityp"}, "Typer", JoinKind.LeftOuter),
...