Hi Teresa,
I do not work with P&L therefore my assumptions might be wrong.
=> still my approach should work for you
- Every month you get a new file => get the latest file from the folder
- The only new data is in column 38 of each sheet => Add only this data to your database!
- All sheets have the same structure => Use a custom function to get the data from each sheet
For the 3 sample sheets it looks like this => Seems to reflect your screenshot.
I took out calculated rows like margin or total. They are the result of calculations, so they do not belong into your database. As I do not work with P&L I might not have taken all necessary rows respectively have included some not necessary rows.
=> That can be adapted in the custom function!
(table)=>
let
//Source = LatestFile,
Data = Table.FirstN(table,124),
#"Removed Other Columns" = Table.SelectColumns(Data,{"Column1", "Column38"}),
SheetInfo = Text.Replace(Text.AfterDelimiter(Text.Combine(List.FirstN(List.Skip(#"Removed Other Columns"[Column1]),3) & List.LastN(#"Removed Other Columns"[Column1],1) & List.Range(#"Removed Other Columns"[Column38],5,2), "|"),","),"Generated date: ",""),
NewData = Table.Range(#"Removed Other Columns",8,91),
#"Renamed Columns" = Table.RenameColumns(NewData,{{"Column1", "Line Item"}, {"Column38", "Line Item Value"}}),
#"Filtered null" = Table.SelectRows(#"Renamed Columns", each ([Line Item Value] <> null)),
#"Replaced Value" = Table.ReplaceValue(#"Filtered null","Net Revenue","Total Net Revenue",Replacer.ReplaceText,{"Line Item"}),
#"Added Category" = Table.AddColumn(#"Replaced Value", "Category", each if Text.StartsWith([Line Item], "Total ") then Text.AfterDelimiter([Line Item], "Total ") else null),
#"Filled Up" = Table.FillUp(#"Added Category",{"Category"}),
#"Filtered Line Items" = Table.SelectRows(#"Filled Up", each not Text.Contains([Line Item], "Margin") and not Text.StartsWith([Line Item], "Total")),
#"Added Custom" = Table.AddColumn(#"Filtered Line Items", "Custom", each SheetInfo),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Fiscal Period End Date", "Currency", "Tab Name", "Statement Generated Date", "Fiscal Period", "Type"}),
#"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"Type", "Currency", "Fiscal Period End Date", "Statement Generated Date", "Fiscal Period", "Tab Name", "Line Item", "Line Item Value", "Category"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Fiscal Period End Date", type date}, {"Fiscal Period", type date}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Statement Generated Date", type date}}, "en-US")
in
#"Changed Type with Locale"
You need to adapt the folder path and potentially can take out the first filtering step:
let
Source = Folder.Files("C:\YourFolderPath"),
#"Filtered PL-" = Table.SelectRows(Source, each Text.StartsWith([Name], "PL-")),
#"Sorted Rows" = Table.Sort(#"Filtered PL-",{{"Date created", Order.Descending}}),
LatestFile = #"Sorted Rows"{0}[Content],
#"Imported Excel Workbook" = Excel.Workbook(LatestFile)[[Name],[Data]],
#"Added Custom" = Table.AddColumn(#"Imported Excel Workbook", "NewData", each fxGetData([Data]))[[NewData]],
#"Expanded NewData" = Table.ExpandTableColumn(#"Added Custom", "NewData", {"Type", "Currency", "Fiscal Period End Date", "Statement Generated Date", "Fiscal Period", "Tab Name", "Line Item", "Line Item Value", "Category"}, {"Type", "Currency", "Fiscal Period End Date", "Statement Generated Date", "Fiscal Period", "Tab Name", "Line Item", "Line Item Value", "Category"})
in
#"Expanded NewData"
Regards,
Matthias
