I hope there will be some fine people that will have time, patience and will to help me on this one.
Excel 365 file is consisted of many Sheets.
They all are Bank statements and are in uploaded file, in Sheets 1-10.
Bank creates them once and they never change afterwards, they just add new Sheets (statements).
Each Sheet is named with a unique number that is always equal to the "Statement No" which is presented in Cell B3 of every Sheet.
Rows 1 and 2, 4 and 5 are merged and always the same.
Row 3 has several cells that are important: B3, C3 and G3. These cells are always filled with data, never empty.
Everything after row 5 is also important, although there might be no data in them in some Sheets.
New Sheets are added manually daily and have the same pattern as described.
I need a Power Query that will automate data (Refresh) from these Sheets into single, new Sheet.
Whenever a new Sheet is added, and when a Refresh is triggered, then Power Query should add data just from the new Sheet.
I created a Sheet called "FINAL" as a sample file how should it look like.
That is data wise, not appearance, colors, fonts...although number and date formats are important to be set properly in PQ, due to further analysing.
Here are the rules that show how the Power Query should gather data:
Header columns should be as follows:
Column A header: Number (from A4+5 merged, from every Sheet is the same)
Column B header: Type of change (B4+5)
Column C header: Debtor name (C5)
Column D header: Account Number (D5)
Column E header: Bank (E5)
Column F header: Requests (F5)
Column G header: Debts (G5)
Column H header: Payment code (H4+5)
Column I header: Purpose of payment (I4+5)
Column J header: The number of the debtor and creditor (J4+5)
Column K header: Claim number (K4+5)
Column L header: Posting date (C1+2)
Column M header: New account balance (G1+2)
Column N header: Statement No (B1+2)
These columns must include this data:
Column A: A6, A7 and as many rows are in Sheets 1-10
Column B: B6, B7 and as many rows are in Sheets 1-10
Column C C6, C7 and as many rows are in Sheets 1-10
Column D: D6, D7 and as many rows are in Sheets 1-10
Column E: E6, E7 and as many rows are in Sheets 1-10
Column F: F6, F7 and as many rows are in Sheets 1-10
Column G: G6, G7 and as many rows are in Sheets 1-10
Column H: H6, H7 and as many rows are in Sheets 1-10
Column I: I6, I7 and as many rows are in Sheets 1-10
Column J: J6, J7 and as many rows are in Sheets 1-10
Column K: K6, K7 and as many rows are in Sheets 1-10
Column L: C3 from Sheets 1-10 respectively
Column M: G3 from Sheets 1-10 respectively
Column N: B3 from Sheets 1-10 respectively
I apologize for not writing more briefly.
Thanks in advance!
Not 100% what you asked for but it could be acceptable for you anyway. The following M-code produces a table that resembles your FINAL sheet. Just point the Source step to your statements file.
All is done by clicking in the user interface. The only tricky bit lies in the #"Added Custom" step where I first extract the three top rows of every sheet, then promote the headers and then keep the bottom row. So you have to read the code 'inside-out'.
let Source = Excel.Workbook(File.Contents("C:\............\statements 2025.xlsx"), null, true), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}}), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Item"}), #"Removed Other Columns" = Table.SelectColumns(#"Removed Errors",{"Data"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.LastN(Table.PromoteHeaders(Table.FirstN([Data],3), [PromoteAllScalars=true]), 1)), #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Expanded Data", "Custom", {"Statement No ", "Posting date", "Initial state", "New account balance"}, {"Statement No ", "Posting date", "Initial state", "New account balance"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Column1", Int64.Type}}), #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Column1"}), #"Filtered Rows" = Table.SelectRows(#"Removed Errors1", each ([Column1] <> null)), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Number"}, {"Column2", "Change"}, {"Column3", "Name"}, {"Column4", "Account Number"}, {"Column5", "Bank"}, {"Column6", "Requestes"}, {"Column7", "Debts"}, {"Column8", "Code"}, {"Column9", "Purpose"}, {"Column10", "debtor / Creditor Nr"}, {"Column11", "Claim"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column12"}) in #"Removed Columns"