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);
| 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. using excel 2019 on windows
thanks in advance for your time and support.
P.S. I have posted the same here https://www.mrexcel.com/board/threads/power-query-consolidate-multiple-files-and-transpose-data.1182206/
Hello,
It would be much better if you upload sample files containing the data.
Where do you get stuck? In other words, what have you tried to do?
The output table you want to have is better if you create using Pivot Table rather than in Power Query. Keep the data in a tabular format and you are in the lead on the track. 
Power Query Get Files from a Folder • My Online Training Hub <-- A good starting point. Make sure you keep the name column showing the file names.
Power Query Archives • My Online Training Hub <-- A bunch of other good articles about Power Query.
Excel Power Query Course • My Online Training Hub <-- A course I can highly recommend. I thought I was skilled enough until I participated in this course. Now I am.
Br,
Anders
Thank you Andres. for your guidance and links. using pivot table for the out table is fine and I wasn't thinking about that previously.
I have combined files using the power query but not clear how to bring the branch name from row to column. the raw data file no different than the contents what I have provided for some specif reason I wont be able to post such files here.
Branch Name starts after the column headers followed by the item names and once all the items sold by the relevant branch and then the next branch comes again.
Hello,
One way to bring out the Branch is like this. In Power Query Editor;
- Go to Add Column menu tab and choose Conditional Column.
- Name this new column to something useful, for example Branch, and set the If statement as follows:
- Column Name = Item Group
- Operator = equals
- Value = null (just type in the word null, as it is)
- Output = Select Column = Item Name
- Else = null
- You have now a new column showing only the BRANCH-information. Click the column header for this new column and go to Transform menu and choose Fill and then Down. You can right click the column header and choose Fill and then Down from the context menu.
- As a final step choose to remove empty rows (null is empty) from Item Group column.
Br,
Anders
Thank you very much Andress. it worked like charm.
