HeadAgainstABrickWall
New Member
- Joined
- Jan 30, 2022
- Messages
- 30
- Office Version
- 365
- Platform
- Windows
Crossposted to Stack Overflow:
I have six sheets in my workbook containing a combined 140 columns, five of which I have set up as connection-only queries, and one which is the actual merge query. All sheets have values in Person ID and start date which are used to merge the worksheets (left join). With the exception of the first sheet (adding an index column for duplicates and a filter column to remove specific types of rows), the merge process is identical. The number of columns does change in each sheet but they are all matched by Person ID and Start Date and are all declared as table ranges.
I am trying to automate this but the Power Query code is 18,500 Characters long without line breaks. Steps are very basic: add index, merge, expand, merge, expand, merge, expand, filter index to remove duplicates, load.
I have been trying to fit the code into my macro for hours with no luck - it keeps throwing "too complex" with line breaks. I am trying to declare each line as a variable and the stringing them together but I'm getting a lot of errors.
Can't add demo code as this contains a lot of variables specific to my job.
All sheets need to be joined as the total columns represents one batch report - these need to be merged sequentially to the first sheet, which contains specific client detail and quotes. It is a horrible system, I know, but fixing it is beyond the scope of my role!
All suggestions appreciated.
I have six sheets in my workbook containing a combined 140 columns, five of which I have set up as connection-only queries, and one which is the actual merge query. All sheets have values in Person ID and start date which are used to merge the worksheets (left join). With the exception of the first sheet (adding an index column for duplicates and a filter column to remove specific types of rows), the merge process is identical. The number of columns does change in each sheet but they are all matched by Person ID and Start Date and are all declared as table ranges.
I am trying to automate this but the Power Query code is 18,500 Characters long without line breaks. Steps are very basic: add index, merge, expand, merge, expand, merge, expand, filter index to remove duplicates, load.
I have been trying to fit the code into my macro for hours with no luck - it keeps throwing "too complex" with line breaks. I am trying to declare each line as a variable and the stringing them together but I'm getting a lot of errors.
Can't add demo code as this contains a lot of variables specific to my job.
All sheets need to be joined as the total columns represents one batch report - these need to be merged sequentially to the first sheet, which contains specific client detail and quotes. It is a horrible system, I know, but fixing it is beyond the scope of my role!
All suggestions appreciated.
