Getting Data from a number of files - Really Slow processing times

Buck2919

New Member
Joined
Jan 7, 2020
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

I have a problem i'm stuggling to overcome, -

I am trying to get data from a number of files and Power BI is really slow.

The folders i am trying to get DATA from are structed in the following way

Year (3 seperate folders) > Month(12 folders) > Insurers(51 Folders) > individual files (Number of files in some cases 71).
(> = Subfolder)

I assume the file struture and number of files PowerBI has to go through to find my data is the reason for the poor speed.

I can't change the file structure.

Each insurer statement file is formatted differently so i need to model these in the editor.

Does anybody know a way to make this possible - either VBA or PowerQuery. I set about getting the data on a individual insurer basis and look to apend it later but it's still taking too long.

Example of a query for 1 insurer -
Rich (BB code):
let
    Source = Folder.Files("\\MyCompany\dfs\IBA\IRec-Prod\Annotated Statements"),
    #"Filtered Rows" = Table.SelectRows(Source, each Date.IsInCurrentMonth([Date modified]) or Date.IsInPreviousNMonths([Date modified], 12)),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Filtered Rows", "Text Between Delimiters", each Text.BetweenDelimiters([Folder Path], "\", "\", 8, 0), type text),
    #"Filtered Rows1" = Table.SelectRows(#"Inserted Text Between Delimiters", each ([Text Between Delimiters] = "Ace and Chubb")),
    #"Inserted Start of Month" = Table.AddColumn(#"Filtered Rows1", "Start of Month", each Date.StartOfMonth([Date created]), type datetime),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Start of Month",{{"Start of Month", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text Between Delimiters", "Insurer"}, {"Start of Month", "Date"}}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns", "Transform File from NIG_Statement_DATA", each #"Transform File from NIG_Statement_DATA"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from NIG_Statement_DATA"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from NIG_Statement_DATA", Table.ColumnNames(#"Transform File from NIG_Statement_DATA"(#"Sample File (6)"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Policy", type text}, {"ClaimNumber", type text}, {"DueDate", type date}, {"EffDate", type date}, {"PaymentDueDate", type date}, {"PaymentAgeing", type text}, {"Reference", type text}, {"Insured", type text}, {"Voucher", Int64.Type}, {"Audit No.", type text}, {"Trans", type text}, {"RecType", type text}, {"CCY", type text}, {"Gross", type number}, {"Commission", type number}, {"Tax", type number}, {"Net", type number}, {"SLAccount", type text}, {"AccountName", type text}, {"Lead Status", type text}, {"Settlement Date", type date}, {"Settlement Confirmation File Name", type text}, {"ID", type number}})
in
    #"Changed Type1"


Thank you very much
Michael
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
I'm pretty certain the easiest way would be to use PowerQuery and create a New Query, From File, From Folder. Then point to the Annotated Statements folder - PQ then provides example data which you can manipulate, this is then applied to all files in structure.

I dont have any data to check this with, but it would be where I'd go.

HTH
 

Buck2919

New Member
Joined
Jan 7, 2020
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Thank you for your response.

Never thought about manipulating the sample file. Thank you i appriciate your resopnse.

Michael
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the feedback - really hope it works.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,428
Members
412,529
Latest member
cTatch
Top