none
Parameterized/ Nested Queries - Load Sales Data and Associated Data

    Question

  • Hi

    I would like to analyse some sales data - lets says the last 2 weeks. Associated with that I want to load product descriptions and customer descriptions.  Is there any way of a) loading the last 2 weeks sales data. Then from distinct products in  a) load only those product descriptions. ( c) same for customer data.

    The issue is how to reuse data from a) without rerunning query a) within query for b) and c)

    one possibility I could see is outputting distinct products as power pivot table and then creating excel table "products". Then using excel table in power query for b) 

    However, AFAIK there is no way of  readjusting size of table.

    Is there a better way of doing this?

    Tuesday, March 18, 2014 7:48 PM

Answers

  • Hi,

    For the first part, where you want to limit the results to the last 2 weeks, you can apply a filter to your tables such as this (assuming SaleDate as a column)

    = Table.FilterRows( CustomersData, each [SaleDate] > DateTime.LocalNow() - #duration(14,0,0,0)

    For the second part, after you filter these rows using the above method, you can merge the result with the product table and the customer using the appropriate key which will give you a merged table with the descriptions that will match the rows returned by the original table (within the 2 weeks window).

    Hope this works for you, if you need more detail please let us know.


    Zafar Abbas

    Wednesday, March 19, 2014 3:37 AM

All replies

  • Hi,

    For the first part, where you want to limit the results to the last 2 weeks, you can apply a filter to your tables such as this (assuming SaleDate as a column)

    = Table.FilterRows( CustomersData, each [SaleDate] > DateTime.LocalNow() - #duration(14,0,0,0)

    For the second part, after you filter these rows using the above method, you can merge the result with the product table and the customer using the appropriate key which will give you a merged table with the descriptions that will match the rows returned by the original table (within the 2 weeks window).

    Hope this works for you, if you need more detail please let us know.


    Zafar Abbas

    Wednesday, March 19, 2014 3:37 AM
  • Thanks for your answer Zafar.

    Can you explain the merge procedure. ( I don't find any documentation of the memory usage).  Lets say I have 50 million rows in my sales data, does the merge just add extra columns to the sales table - won't this be very memory intensive? Or does the columnar data base somehow make this memory efficient?

    Thanks

    Sean

    Wednesday, March 19, 2014 1:12 PM
  • Hi Sean,

    If both queries depend on the same Server/Database, Power Query will translate the Merge of the queries into T-SQL statements that will be evaluated on the Server. This should be just as efficient as performing the JOIN operation in the server.

    Thanks,
    M.

    Sunday, March 23, 2014 6:40 PM
  • Hi Miguel

    I was really referring to the excel data model usage rather than the query itself ( though that is useful to know).

    It would seem more efficient ( to ME!) to maintain sales ,product, and customer tables separately in excels data model ( with a relationship between the tables), rather than copy all the product details on every line of the sales, which seems to be what merge does. In  other words I want to have a star schema rather  than a single joined  table.  This seems most efficient, though I don't understand how columnar databases work and compress their data.

    So what I believe I should create is a star schema ( sales, product, customer...) containing data  only for  the last 2 weeks of sales data. I don't need to load every single customer I've ever dealt with or every product I've ever sold.   

    [ As I said in my first post - this would be the sql pseudo-code I would do

    querysales2weeks: = select * from sales where salesdate>today()-14

    select queryproductdistinct= select distinct (productkey) from querysales2weeks

    queryproduct = select * from product where productkey in queryproductdistinct ...

    SO in the excel data model I would have a cutdown salestable anda cut down product table ( and customer table etc)

    ]

    Thanks

    sean

    Sunday, March 23, 2014 7:22 PM