none
Power Query to import data from current Excel workbook RRS feed

  • Question

  • Hi!

    Here's the situation. There's an Excel workbook that is manually edited by an employee. It consists of several sheets with ranges of common type. Each sheet in the workbook represents transactions of definite product within a calendar year. I created a new Excel workbook and built a Power Query that combines those sheets into single table in Power Pivot data model. There are about 50 thousands of rows in the model table at the moment and each year brings about 20 thousands more. This PQ-powered file can now be connected to other data sources (SSAS Tabular in my case), some adjustments made in the data model and build Pivot tables and/or visualizations as needed. Great!

    But it looks like moving combined data straight into SSAS Tabular is a more convenient way to prepare data for future use both in Excel/PBID and SSRS. Since number of rows is far away from Excel worksheet limits I can create a proxy Excel file and import date onto worksheet. And then use this proxy file as data source for SSAS Tabular. In this case one need to update additional proxy file manually to provide updated data to SSAS.

    I'm curious is that possible to let Power Query import data from current Excel file to get rid of necessity of proxy Excel file?

    Thank you!

    Friday, January 22, 2016 1:36 PM

Answers

  • Hi Egor,

    I'm using a different method in these cases: Reference the current workbook via "From-Folder" This will let you access also sheets and not just tables and named ranges from your current workbook.

    Only restriction in this case: You have to save your changes in your current workbook in order to make them available in your queries there. But sometimes this is even an advantage - depends on the specific use case.


    Imke Feldmann TheBIccountant.com

    Wednesday, January 27, 2016 7:32 AM
    Moderator

All replies

  • Have you tried From Table?

    Ehren

    Friday, January 22, 2016 7:44 PM
    Owner
  • Thank you, Ehren, for suggestion! It leads me to Excel.CurrentWokbook() usage. But some source file features do not let to use"From Table" trick directly. 

    The point is that "From Table" creates an Excel table with columns, formatting, etc. This is not the case since I don't want to modify employee workflow on entering data. I have to remain an "invisible man" :) 

    There's an answer for exact the same issue in The BookIt's Chapter 5 "Aggregating Excel worksheets" that describes Excel.CurrentWorkbook() usage both for Excel tables/named ranges and worksheets with the help of setting Printing area for unnamed ranges handling. 

    I slightly modify the method described in the book since I need to unpivot data on each worksheet before aggregation. So in my case here's the workflow to get pivoted data in unnamed ranges on different worksheets: 

    1. Manually set Print area for each worksheet. Print area should contain all the columns used for aggregating 

    1. Start building query with Excel.CurrentWorkbook(), get the table of Excel tables/named ranges in the book, filter out thoserows(i.e. tables/ranges) you don't need anymore 

    1. Continue building query for grabbing data from one worksheet  

    1. Make a copy of this query and convert it to function 

    1. Get back to the query in p.2 and remove all the steps after filtering out the table of Excel tables/named ranges 

    1. Using values in Name column add new column(s)containing values of variables you should later pass to a function created in p.4 

    1. Add new columns with function call 

    1. Remove other columns and expand tables in resulting column 

    My first version of those two queries was too slow and I spent some time to try to find the reason of performance hit. But then (btw, are there any ways to profile Power Queries?) I started from scratch and decided just to cut all the unneeded data off as soon as they appears to be. And it worked! 

    • Edited by Egor Ushakov Tuesday, January 26, 2016 9:05 PM text correction
    Tuesday, January 26, 2016 9:02 PM
  • Hi Egor,

    I'm using a different method in these cases: Reference the current workbook via "From-Folder" This will let you access also sheets and not just tables and named ranges from your current workbook.

    Only restriction in this case: You have to save your changes in your current workbook in order to make them available in your queries there. But sometimes this is even an advantage - depends on the specific use case.


    Imke Feldmann TheBIccountant.com

    Wednesday, January 27, 2016 7:32 AM
    Moderator
  • Hi Imke,

    Looks like with "From Folder" action we come finally to Excel.Workbook() function call that requires absolute file path as argument. Although one of examples of Excel.Workbook() usage consumes simple filename without path, try to use it in real world and you most likely got error:

    DataFormat.Error: The supplied file path must be a valid absolute path.

    And, yes, Excel.Workbook() works with the most recent saved file version. So there's a need to save file before refreshing aggregating PQ. 

    Thank you, Imke, for reply and your excellent blog!

    Wednesday, January 27, 2016 6:41 PM