+ Reply to Thread
Results 1 to 7 of 7

Power Query from Folder - BUT not consolidating the top rows

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    Nashville TN
    MS-Off Ver
    365
    Posts
    4

    Power Query from Folder - BUT not consolidating the top rows

    Hello,
    I am new to the forum and really a beginner in Excel.

    I am trying to combine into 1 Excel Sheet about 20 files that are in the same folder (see file example attached).
    I am using Power Query.
    All the files have the same structure and I may add other files with the same structure to the folder other the coming months.
    My challenge is that I want to consolidate the rows only starting from row 6 (data) and re-use Header from row 5.

    Can you help me?

    Thx,
    Mikaël
    Last edited by Mikael37167; 12-10-2018 at 04:08 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query from Folder - BUT not consolidating the top rows

    What you'd do is, perform transformation using sample file.

    Steps:
    1. Combine files from folder using Get Data -> From File -> From Folder. Choose Combine & Edit option on window.
    2. Now in the editor, select "Transform Sample File from..."
    3. Choose column 2 and filter out null (Remove empty).
    4. Promote first row to header.

    See below for sample M code for "Transform Sample File from ...". Adjust column data type as needed.

    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    12-10-2018
    Location
    Nashville TN
    MS-Off Ver
    365
    Posts
    4

    Re: Power Query from Folder - BUT not consolidating the top rows

    Thx for your support. That's exactly what I was looking for.
    However, I was able to use the 1st row as header (Offer, Country, Local Launch Manager...) but when I load, it repeats the header within the sheet.
    How can I do to not repeat it?
    In addition, my files will be in a BoxSync folder. Will it work?
    Thx

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query from Folder - BUT not consolidating the top rows

    Make sure that you use query from folder. And use Combine & Edit.
    Then make sure you apply transformation to auto-generated "Transform Sample...." query, and NOT the result query (combined).

    Transformation applied to sample will propagate to each file that has same file/data structure and will eliminate duplicate headers.

    I'm not familiar with BoxSync folder, but if it's mapped to local drive. PQ should not have an issue.

    If you need more specific help in transformation, I'd recommend you zip some sample files with appropriate desensitized data (but with same data type).

    To upload file, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

  5. #5
    Registered User
    Join Date
    12-10-2018
    Location
    Nashville TN
    MS-Off Ver
    365
    Posts
    4

    Re: Power Query from Folder - BUT not consolidating the top rows

    I am not sure to find it. The problem remains (see attached file as suggested).
    Meanwhile, It looks like that the cells format is not maintained. Is it possible to maintain it?
    I am trying to build a database that will contain Main Geo & Winning Launches Leaders (see attached file). Is it possible to cross the Consolidation Test.XLS and country table.XLS? The country table may change within the year.
    Again, appreciate your support.
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query from Folder - BUT not consolidating the top rows

    Ok, so looking at your attempt. You did not apply steps at correct stage.

    All transformation steps, excluding merge and changing data type should occur in "Transform Sample File from Hero Files" query, and not in "Hero Files" query.
    0.JPG

    Data type change should be applied at last stage of "Hero Files".
    See attached sample (In the editor, go to "Data source setting and change source to your actual folder).
    1.JPG

    I'm not certain how you want to use "Country Table.xlsx". Is it simple left join using Country as key (i.e. VLookup)?

    Edit: I won't have access to PC for rest of the day, but should have time to look at it tomorrow.
    Attached Files Attached Files
    Last edited by CK76; 12-10-2018 at 05:02 PM.

  7. #7
    Registered User
    Join Date
    12-10-2018
    Location
    Nashville TN
    MS-Off Ver
    365
    Posts
    4

    Re: Power Query from Folder - BUT not consolidating the top rows

    I was thinking to add 2 columns in between Country & Launch Manager columns within Consolidation Test File and insert Vlook-up formulas but I am not sure this is the best way to do it. Do I do it in the consolidated sheet or do I implement a formula in the query? Your thought?

    Your previous file is working very well and this is what I need but I am not sure I understood your explanation to remove headers duplicates and keep formatting. I compared your & my file and I don't see the differences in the Query steps...
    Last edited by Mikael37167; 12-10-2018 at 05:38 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel Power Query - Only New Files Using 'From Folder'
    By rabbit_post in forum Excel General
    Replies: 4
    Last Post: 11-12-2018, 06:07 AM
  2. [SOLVED] Select Rows between blank rows using Power Query
    By beaglesBuddy in forum Excel General
    Replies: 5
    Last Post: 08-17-2018, 08:30 PM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. Replies: 11
    Last Post: 08-01-2017, 06:08 AM
  5. Excel 2010 Power Query from folder with CSV
    By davidbrennan in forum Excel General
    Replies: 0
    Last Post: 03-22-2017, 09:56 AM
  6. Power Query - Get external data from Folder
    By crainaud in forum Excel General
    Replies: 0
    Last Post: 03-24-2016, 12:49 PM
  7. Remove Unique rows in Power Query
    By danielmorrison in forum Excel General
    Replies: 2
    Last Post: 03-11-2016, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1