none
From Folder, Multiple Files RRS feed

  • Question

  • Hi, I have a folder where a report will be saved to daily. It's the same report, with updated numbers. However, the report data doesn't really start till row 5, and honestly, I only need the last row data, and columns 8, 10 and 12. How do I set the power query to only pull those 3 fields from the spreadsheet and each subsequent spreadsheet that is saved in the folder? Please be detailed, I'm practically brand new to power query. I got a test to show the data I want, but it wouldn't do it for each spreadsheet nor update even when I added more spreadsheets to the folder.

    Saturday, February 9, 2019 8:45 PM

Answers

  • There are numerous samples in the web for importing from folder that will tell you that you have to create a sample query that works on one file and transform that to a function that will be applied to each of the files within that folder. Just browse through them to find one that fits your current learning level.

    The specific requirements of your transformations can be achieved like so:

    1) Get only the last row of the table: 

    2) Get the columns 8, 10 and 12:

    I suspect that they have different column names, so you want to retrieve them from their position?

    Step 1: Demote Headers - that will make your columns be named by numbers

    Step 2: Check column 8, 10 and 12 and remove other columns:


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, February 10, 2019 7:19 PM
    Moderator