New User Questions

Any topic that doesn't fit in elsewhere
kdsan
Posts: 3
Joined: Thu Sep 22, 2016 3:48 am

New User Questions

Postby kdsan » Thu Sep 22, 2016 4:35 am

I'm knew to both PowerPivot and PowerQuery.

I've used connections SQL to query (before import) specific tables and only pull the fields I need, keeping in mind I want to use this model for future analysis, not just my immediate need. I did this all in PowerPivot. Attached is a picture. These tables contain a LOT of information. For right NOW, I only want to take a look at a distinct buyers of a specific product, within a specific time frame. Later I would like to collect their contact information so we can market to them. I don't see how, in PowerPivot, I would limit the data to just look at the one product or limit the time frame. So I figure I must do so in PowerQuery.... except I can't access the data model i just created. I searched online that you can move data from powerQuery to PowerPivot but haven't found anything that says how to move it the other way.

Should I have started in PowerQuery instead? Can I create relationships there? If so, then why not just work out of PowerQuery only? I guess I'm just having trouble wrapping my head around how I should set this up and what I do where.

I set it up this way so that if tomorrow I need to run profiles on buyers of product B, I would have to create an entirely new model. Any pointers are appreciated.
Attachments
PowerPivotScreenShot.PNG
PowerPivotScreenShot.PNG (49.8 KiB) Viewed 1405 times

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: New User Questions

Postby MattAllington » Thu Sep 22, 2016 2:47 pm

I suspect you are missing some key conceptual points, but it is not clear to me exactly what you know and what you don't.

Generally, you should
1. Only load the columns of data you need in your sales table.
2. Load all the columns you need now and in the future into your lookup tables (the ones with the arrow pointed towards them)
3. Load all the rows of data for your reporting time horizon.

I generally do this and then don't change it again unless something significant changes.

You then analyse just the data you want for the current report using the data you have loaded.

To look at specific buyers, I would write a measure DISTINCTCOUNT(sales[cust id]) and use that in a pivot table along with relevant columns from the calendar table. There is no need to remove any of he other data in your data model.

kdsan wrote: I don't see how, in PowerPivot, I would limit the data to just look at the one product or limit the time frame. So I figure I must do so in PowerQuery.....


You have a reason for asking this, but I actually don't understand why you think this is an issue. The data model contains all the data you loaded, but the pivot table only shows the relevant data you are analysing. You can use filters and slicers in the pivot to further filter the data so that you are analysing just the data of interest. There is no need to change what data is loaded using either power query or power pivot. The approach is "load all the data, analyse a sub set using filters in your pivot table"

Some other advice for you
Rename your tables. Calendar is better than BasicCalendarUS
Rename your table columns to be "business" names. These will appear in your reports, so "Product Type" is better than prd_nf_product_type
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

kdsan
Posts: 3
Joined: Thu Sep 22, 2016 3:48 am

Re: New User Questions

Postby kdsan » Fri Sep 23, 2016 12:47 am

Matt - thanks for your reply.

What I know and what I don't? Not much - I'm a total newbie. Don't know anyone who's used these tools so I'm trying to learn on my own and ask questions here.

Generally, you should
1. Only load the columns of data you need in your sales table.
2. Load all the columns you need now and in the future into your lookup tables (the ones with the arrow pointed towards them)
3. Load all the rows of data for your reporting time horizon.


I thought that's what I did ... with the exception of the calendar table. That's something I came across in the marketplace - not sure if i'll use it yet. Does it appear to you that I did something different than what you stated?

To look at specific buyers, I would write a measure DISTINCTCOUNT(sales[cust id]) and use that in a pivot table along with relevant columns from the calendar table. There is no need to remove any of he other data in your data model.


Where do I create this measure? In the sales table? If so, I'm getting the same count for all records. Do I need to sort the ID's first for the measure to work?

Some other advice for you
Rename your tables.

Thanks for the tip! :D

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: New User Questions

Postby MattAllington » Fri Sep 23, 2016 1:20 am

My 3 steps were to give you direction. If that is what you did, then great. But you talked about removing some data - no need to do that.

Measures can be written from the Excel UI or from the Power Pivot UI. Regardless, you have to store them in a table. They should be stored in the table from where the data comes from. In this case you are counting a column in the sales table, so yes it goes there. But I always teach students to set up a pivot table and write the measure there (in Excel). I think this is the bit you are missing. Measures will return different results depending on the filter context. Take a column from your customer table (say country) and put it on rows in the pivot. Then write the measure and add it to the pivot table. You should get different results for each country.

For US$15, you can learn a lot, fast by reading my book. Http://xbi.com.au/learndax

I wrote this book to help Excel users get started.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

kdsan
Posts: 3
Joined: Thu Sep 22, 2016 3:48 am

Re: New User Questions

Postby kdsan » Fri Sep 23, 2016 6:42 am

Just got it - thanks!


Return to “General”

Who is online

Users browsing this forum: No registered users and 1 guest