Hi experts, I am relatively new to power query and I have a very complicated scenario (at least I believe so).
With my limited knowledge on power query I couldn't achieve my desired output.
I humbly request you to provide me solution or a workaround to achieve my expected results.
I have multiple files as below,
Sales-Jan.xlsx
Sales-Jan-A.xlsx
Sales-Feb.xlsx
Sales-Mar.xlsx
Sales-Mar-A.xlsx
Sales-Mar-B.xlsx
Sales-Apr.xlsx
Sales-May.xlsx
Sales-May-A.xlsx
Sales-Jun.xlsx
etc tec, and goin for every month.
Each file contains more than 100 rows,
All the files has identical column names and, no of columns and same column order
Every file contains similar data as below (for eg: Assume Sales-Jan.xlsx file);
I would wanted all of those files combined and transformed and transposed as below
in the output table must have below points,
branch names contains unwanted text (see bold text and its not unique, I hope can do with split text with delimiter)
column names must be taken from the file name, eg : Jan, Feb, Mar etc
each and every file's net sales value plotted into its relevant column, eg: Jan net sales into Jan Column , Feb Net sales into Feb Column
negative figures must be converted to null (See bold cell values in the raw data table)
a custom column must be added to count non blank cells for each row (In the last)
this final table used as a raw data for further calculation by merging to existing queries.
thanks in advance for your time and support.
Also posted here https://www.myonlinetraininghub.com...consolidate-multiple-files-and-transpose-data
With my limited knowledge on power query I couldn't achieve my desired output.
I humbly request you to provide me solution or a workaround to achieve my expected results.
I have multiple files as below,
Sales-Jan.xlsx
Sales-Jan-A.xlsx
Sales-Feb.xlsx
Sales-Mar.xlsx
Sales-Mar-A.xlsx
Sales-Mar-B.xlsx
Sales-Apr.xlsx
Sales-May.xlsx
Sales-May-A.xlsx
Sales-Jun.xlsx
etc tec, and goin for every month.
Each file contains more than 100 rows,
All the files has identical column names and, no of columns and same column order
Every file contains similar data as below (for eg: Assume Sales-Jan.xlsx file);
| Item Group | Item Code | Item Name | UOM | Sales | Sales Return | Net Sales | Closing Stock |
| BRANCH-A-XXXXXXX | |||||||
| A | ABC1 | NAME1 | NOS | 10.00 | 2.00 | 8.00 | 1.00 |
| A | ABC2 | NAME2 | NOS | 15.00 | 3.50 | 11.50 | 10.00 |
| A | ABC3 | NAME3 | KG | 200.00 | 44.50 | 155.50 | 350.00 |
| A | ABC4 | NAME4 | PKT | 100.00 | 105.00 | (5.00) | 25.00 |
| B | BA1 | NAME8 | L | 150.00 | 45.50 | 104.50 | 100.00 |
| B | B2A | NAME8 | L | 28.00 | 2.50 | 25.50 | 5.00 |
| BRANCH-B-XXXXX | |||||||
| A | ABC2 | NAME2 | NOS | 300.00 | 15.80 | 284.20 | 5.00 |
| A | ABC3 | NAME3 | KG | 25.00 | 4.00 | 21.00 | 2.00 |
| C | CB1 | NAME9 | NOS | 255.50 | 3.00 | 252.50 | 400.00 |
| C | CB2 | NAME10 | NOS | 38.50 | 45.00 | (6.50) | 25.00 |
| BRANCH-C-XXXXX | |||||||
| B | BA1 | NAME8 | L | 45.50 | 50.00 | (4.50) | 35.00 |
| B | B2A | NAME8 | L | 48.25 | 30.00 | 18.25 | 85.00 |
| A | ABC4 | NAME4 | PKT | 30.00 | 25.00 | 5.00 | 240.00 |
| C | CB2 | NAME10 | NOS | 25.00 | 4.00 | 21.00 | 45.00 |
I would wanted all of those files combined and transformed and transposed as below
| Item Code | Branch | Jan | Feb | Mar | Apr | May | Jun | Count of Non Blank Cells |
| ABC1 | BRANCH-A | 8.00 | 85.00 | 35.00 | 32.00 | 50.00 | 5 | |
| ABC2 | BRANCH-A | 11.50 | 25.00 | 80.00 | 100.00 | 52.00 | 1.00 | 6 |
| ABC3 | BRANCH-A | 155.50 | 45.00 | 35.00 | 10.00 | 4 | ||
| ABC4 | BRANCH-A | 15.00 | 10.00 | 2 | ||||
| B2A | BRANCH-A | 25.50 | 45.00 | 84.00 | 98.00 | 32.00 | 4.00 | 6 |
| BA1 | BRANCH-A | 104.50 | 39.00 | 820.00 | 45.00 | 87.00 | 8.00 | 6 |
| ABC2 | BRANCH-B | 284.20 | 897.00 | 486.00 | 58.00 | 4 | ||
| ABC3 | BRANCH-B | 21.00 | 48.00 | 54.00 | 64.00 | 9.00 | 4.00 | 6 |
| CB1 | BRANCH-B | 252.50 | 8.00 | 46.00 | 48.00 | 96.00 | 5 | |
| CB2 | BRANCH-B | 468.00 | 6.00 | 864.00 | 64.00 | 4 | ||
| ABC4 | BRANCH-C | 5.00 | 100.00 | 57.00 | 97.00 | 13.00 | 22.00 | 6 |
| B2A | BRANCH-C | 18.25 | 95.00 | 58.00 | 99.00 | 100.00 | 5 | |
| BA1 | BRANCH-C | 65.00 | 54.00 | 9.00 | 856.00 | 5.00 | 5 | |
| CB2 | BRANCH-C | 21.00 | 71.00 | 525.00 | 75.00 | 5.00 | 5 |
in the output table must have below points,
branch names contains unwanted text (see bold text and its not unique, I hope can do with split text with delimiter)
column names must be taken from the file name, eg : Jan, Feb, Mar etc
each and every file's net sales value plotted into its relevant column, eg: Jan net sales into Jan Column , Feb Net sales into Feb Column
negative figures must be converted to null (See bold cell values in the raw data table)
a custom column must be added to count non blank cells for each row (In the last)
this final table used as a raw data for further calculation by merging to existing queries.
thanks in advance for your time and support.
Also posted here https://www.myonlinetraininghub.com...consolidate-multiple-files-and-transpose-data
Last edited by a moderator:
