Hello,
Thank you in advance for any help as I am relatively new to Power BI. I have ~70 excel files with each file being about 15mb with a few new files being generated monthly. Each file contains information that runs from the beginning of the year with some files going farther back than the beginning of the year. While loading the information, I need to grab the most recent version of the file for the month (Ex: 201808_V1, 201808_V2, 201808_V3). I am using a partition based on the date created of the file and filtering to retain the most recent file for the given month. Within the data, I need to use the monthly information from the most recent file that includes that same information. I am creating another index to filter out this information. My problem is that it takes ~40 minutes to load the information and virtually renders my computer unusable during this time and I can't do any work. It also needs to reload all of the data when I try to add a new step to the query. So I have two questions:
1) Is there a way to throttle how much CPU usage power BI uses
2) Is there a way to optimize this query:
let
Source = Folder.Files("S:\programteamsites\CRSP\Reports\Data_Sources\Accounting_Uploads"),
#"Duplicated Column" = Table.DuplicateColumn(Source, "Name", "Filename"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Filename", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Filename.1", "Filename.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Filename.1", Int64.Type}, {"Filename.2", type text}}),
Partition = Table.Group(#"Changed Type", {"Filename.1"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date created", Order.Descending}}), "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Name", "Index", "Date created", "Content"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Name", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Index] = 1)),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Accounting_Uploads (2)", each #"Transform File from Accounting_Uploads (2)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Filename.1","Name","Date created","Transform File from Accounting_Uploads (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Accounting_Uploads (2)", Table.ColumnNames(#"Transform File from Accounting_Uploads (2)"(#"Sample File (2)"))),
RemoveDups = Table.Distinct(#"Expanded Table Column1", {"Item Mth","Filename.1"}),
Partition1 = Table.Group(RemoveDups , {"Item Mth"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Filename.1", Order.Descending}}), "Index",1,1), type table}}),
LookupTable = Table.ExpandTableColumn(Partition1, "Partition", {"Filename.1","Index"},{"Filename.1","Index"}),
Lookup = Table.NestedJoin(#"Expanded Table Column1",{"Item Mth", "Filename.1"},LookupTable,{"Item Mth", "Filename.1"},"NewColumn",JoinKind.LeftOuter),
ExpandedIndex = Table.ExpandTableColumn(Lookup, "NewColumn", {"Index"}, {"Index"}),
#"Filtered Rows1" = Table.SelectRows(ExpandedIndex, each [Index] = 1),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Project Code", type text}, {"Project Name", type text}, {"PM", type text}, {"Development Capitalization", type text}, {"Task Name", type text}, {"SumOfQuantity SUM", type number}, {"TF Date", type date}, {"GA Date", type date}, {"Rate", type number}, {"Capitalized Amount", type number}, {"Item Mth", type date}, {"Project Class", type text}, {"Cap", type text}, {"Product Line", type text}, {"Acctg Type", type text}, {"SDO Initiative", type text}, {"Capitalization Type", type text}})
in
#"Changed Type1"
