Hi,
I have an Excel file (in old format, .xls) that is produced every night from the backend databases and it contains 33 worksheets, that are lists (Power Query reads them as tables but with a delay until it opens all tables). They contain records from a few dozens up to 10000 records. So, we have 33 summary tables up to some detail.
I want only 2 of the 33 tables to be loaded in another workbook through Power Query.
When I create a new Power Query in a new workbook, it delays significantly. It reads all 33 lists and produces 33 tables in 5 to 10 minutes.
The method I'm using is:
Source = Excel.Workbook(File.Contents(FilePath), null, true),
rgn = Source{[Name=WorkshhetName]}[Data],
I replaced the actual names with FilePath and WorksheetName.
The same delay appears when I say RefreshAll, it takes again 5 to 10 minutes to be completed.
Is there q quick way with Power Query to get only the 2 from the 33 tables of this Excel file?
Thank you
Nick
Hi Nick,
Have you tried using Files From > Folder? Then you can choose the two tables you want to import.
Mynda
Thank you for your reply. I think with the folder it worked. It does a quick update.
But if the folder contains some dozens of Excel files, will I have delays? For now, I have tested the file with the 33 tables to be alone in a subfolder.
Also, to tell the truth, I don't understand exactly the structure with folders and subfolders that Power Query constructs in the workbook queries navigator window.
It produced the following:
let
Source = Folder.Files("C:DataNew folder (3)"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Data_Source.xls")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from New folder (3)", each #"Transform File from New folder (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from New folder (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from New folder (3)", Table.ColumnNames(#"Transform File from New folder (3)"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Product Type", type text}, {"Co.", type text}, {"Portfolio", type text}, {"SLIP No", Int64.Type}, {"Trade Date", type date}, {"Bank", type text}, {"CCY Bought", type text}, {"AMT Bought", type number}, {"CCY Sold", type text}, {"AMT Sold", type number}, {"FWD Agreed", type number}, {"FWD Now", type number}, {"Value Date", type date}, {"In Days", Int64.Type}, {"Gain/Loss ($)", type number}, {"Spot Then", type number}, {"Spot Now", type number}, {"Type", type text}, {"PREM AMORT.", type number}, {"UNAMORTIZED", type number}, {"PREMIUM", type number}, {"REPRICING", type number}, {"Daily Amortization", type number}, {"Monthly Amortization", type number}, {"Annual Income", type number}})
in
#"Changed Type"
Hi Nick,
Best to have a folder that only contains the workbooks you want to get data from. This should be easy enough for you to construct.
Power Query automatically generates the code for getting files from a folder so you don't have to understand the complexities of it 🙂
Mynda
OK, Mynda. Thanks a lot for your replies.
Hi,
I have a speed query also.
I'm using excel 2016 and the data is in a sheet within my workbook. the data is 144000 rows.
The Query creates ok.
When editing my query I can add columns etc and the result shows as expected and the speed is pretty quick, within minutes, however when I then press the load/load to button it takes about 2 hours to load! the load is to the managed data area via connection not to a new table
Is there anything I can do to speed it up?
