none
How to use Power Query to load data from a single source into multiple tables RRS feed

  • Question

  • Hi all,

    I have a requirement to load my data into three different data models using Power Query. Is that possible?

    My source is a SharePoint survey list, with similar questions like:

    1) Course lecturer - John Doe

    1a) The course was useful (rate 1 to 5)

    1b) The lecturer displayed good knowledge of topic (rate 1 to 5)

    2) Course Lecturer - Mary Brown

    2a) The course was useful (rate 1 to 5)

    2b) The lecturer displayed good knowledge of topic (rate 1 to 5)

    I would like to split the data into separate data models (one for John Doe; another for Mary Brown), so that I can compare the different lecturers. Other than running separate surveys for each of them, I thought of using Power Query to transform the data. Is it possible?

    Thanks.

    Regards

    GM

    Tuesday, September 9, 2014 1:27 AM

Answers

  • Yes, this is possible.

    1. Start with a single query that returns you the data for all lecturers.
    2. Right-click on the "all lecturers" query in the queries pane, and choose Reference, once for each lecturer. This will create a query for each lecturer.
    3. Open the query for each lecturer and filter the data so that only that lecturer's results are visible.
    4. Click "Close & Load To..." for each lecturer's query  to load the data into the data model. This will create a data model table for each lecturer.

    If your question is more about how to transform such a survey list into a table that can be easily filtered, please provide an example of how the list shows up in Power Query.

    Ehren


    Tuesday, September 9, 2014 5:03 PM
    Owner

All replies

  • Yes, this is possible.

    1. Start with a single query that returns you the data for all lecturers.
    2. Right-click on the "all lecturers" query in the queries pane, and choose Reference, once for each lecturer. This will create a query for each lecturer.
    3. Open the query for each lecturer and filter the data so that only that lecturer's results are visible.
    4. Click "Close & Load To..." for each lecturer's query  to load the data into the data model. This will create a data model table for each lecturer.

    If your question is more about how to transform such a survey list into a table that can be easily filtered, please provide an example of how the list shows up in Power Query.

    Ehren


    Tuesday, September 9, 2014 5:03 PM
    Owner
  • Hi Ehren,

    Found this post searching for a similar solution.  The main table that's loaded to excel is the source for the derivative queries initiated with the reference command.  However, the derivative queries execute all of the statements of the original "referenced" query.  This is not much better than cut/paste the statements of the original query. Copy the original exported table to another table and use that as source for each of the derivative queries.  Cut and paste once and derivative queries do not execute the statements of the original query.  Is this reasonable?!  Many thanks.

    Sunday, July 24, 2016 7:06 PM
  • It depends on what you're trying to do. Using "Reference" means that if you need to update the steps of the base query, you only have to do so in one place (rather than updating all the copied versions as well).

    Round-tripping through Excel means your dependent queries may not be in sync with the latest results of the base query.

    Ehren

    Monday, July 25, 2016 5:22 PM
    Owner
  • Yes, I see your point(s).  Thank you.

    Tuesday, July 26, 2016 12:25 AM