• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query - Consolidate Multiple files and Transpose data|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Power Query - Consolidate Multiple files and Transpose data|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryPower Query - Consolidate Multiple …
sp_PrintTopic sp_TopicIcon
Power Query - Consolidate Multiple files and Transpose data
Avatar
Rajeevaon R

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
1
September 19, 2021 - 2:16 pm
sp_Permalink sp_Print

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/.....a.1182206/

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
September 19, 2021 - 6:22 pm
sp_Permalink sp_Print

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. Cool

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

Avatar
Rajeevaon R

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
3
September 19, 2021 - 9:08 pm
sp_Permalink sp_Print

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.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
September 20, 2021 - 4:35 am
sp_Permalink sp_Print

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

Avatar
Rajeevaon R

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
September 19, 2021
sp_UserOfflineSmall Offline
5
September 20, 2021 - 10:59 pm
sp_Permalink sp_Print

Thank you very much Andress. it worked like charm.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: RAMEZ ATTAR, Jeanine Hagge, Uwe von Gostomski, Tiffany Kang, Scot C
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27293

 

Member Stats:
Guest Posters: 49
Members: 31917
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.