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

[Buck2919]

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 -

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

 

[pjmorris]

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]

Hi,

Thank you for your response.

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

Michael

 

[pjmorris]

Thanks for the feedback - really hope it works.