PowerPivot: Quick Tip regarding Copy-Pasting Tables in PowerPivot 2010

Standard

Power Pivot Paras Doshi Microsoft Business Intelligence Excel 2010I was working on couple of PowerPivot models recently and both models needed a Date Table. So here’s what I did:

1. Imported the filtered version of the Date Table (DateStream) from Azure Data Market. Added Calculated Columns to the table. Let me call this Table1

2. I copied entire Table1 and pasted it to an another PowerPivot model – let’s call that Table2

 

After this:

And I went to happily create data models! Later, I while testing the model (that had Table2), I realized that it had lost the “formulas” – in other words when I copy-pasted from Table1 to Table2 in PowerPivot – it did not copy-paste the formulas but it just pasted the values.

So, 1. Copy Pasting a Table in a PowerPivot does not copy-paste’s the Formula’s in the columns.

And also it does not keep the “Data connection”. In this case, Table1 was connected to Azure Data source and I could “refresh” data if I chose to. But Table2 was “Static” – In other words, It lost the data connection with the data sources. So I could not “refresh/update” data from the data source if need be.

S0, 2. If you copy Paste a Table in a PowerPivot, the newly copied table does not keep the “Data connection” to data source.

 

I have also posted this here: http://beyondrelational.com/modules/1/justlearned/tips/17976/power-pivot-copy-paste-a-table-in-a-powerpivot-does-not-copy-pastes-the-formulas.aspx

New Journal Article Published – Title: “Building an Ideal Tabular Model for Power View reports”

Standard

Part 2/2 of the series on building Ideal Models for Power View reports is live!

Summary: “In this article, we will first compare the PowerPivot and Tabular models, which will help you choose between these two models for your scenarios, and then we will study the reporting properties in a Tabular Model that you can configure to build an Ideal model for Power View reports.”

Read here: SolidQ Journal: Building an Ideal Tabular Model for Power View reports by Paras Doshi

Blog-Post about Part 1 is here: SolidQ Journal: Building Ideal PowerPivot Model for Power View reports

 

If you have any feedback or comments, please drop a comment or contact me.

PowerPivot Model: Why am I not seeing “Month Names” in correct logical order?

Standard

This blog post is for people who have seen the reports built on PowerPivot model where the Month Names are not in correct logical order. So instead of¬† “January, February, March, April …” (which is correct logical order), the order in the report would be displayed as “April, February, January, March..” (which is NOT correct).

This is what I am talking about:

powerpivot model month name not sorted correctly

Here, Month names are not sorted correctly, right? So how do we solve this? Let’s see this in this blog post!

Understanding the Sample Data-set

Optional: Download a sample data-set to practice what’s described in this blog-post: Download – Paras Doshi Blog’s sample data set

Now the data looks like this:

DateDaily New numberMonthMonth Name
1/1/2012 0:0001January
1/2/2012 0:0001January
1/3/2012 0:0001January
1/4/2012 0:0001January
1/5/2012 0:0001January
1/6/2012 0:0001January
1/7/2012 0:0001January
…..………………

Note that “Daily new number” is used for the demo purpose. I had to anonymize the data before I could share it with you all!

Originally, the table had just two columns but since I wanted to add “time Intelligence” to the model. I added two columns Month and Month Name.

Now here’s the DAX behind these two columns:

ColumnDAX
MonthMONTH(‘Sample Table'[Date])
Month NameRELATED(Month[MonthName])

There’s a relationship between Month Column of “Sample Table” and Month column of “Month” table

And here’s what Month Table looks like:

MonthMonthName
1January
2February
3March
4April
5May
6June
7July
8August
9September
10October
11November
12December

Creating a Report on Top of Sample Data-set.

Now if you create the report of Month Name vs Daily New Number, it will look like:

powerpivot model month name not sorted correctly

Oops, Problem detected: Month name are not in correct order.

So now let’s solve it.

Solution to sort month name correctly

Let’s solve this issue in our PowerPivot Model. Here’s what you do:

1. Go to PowerPivot model, Select “Month Name” column from the sample table

2 Toolbar > Home > Sort By column > click on “Sort by column”

3. And set the properties in the dialog box as:

powerpivot model sort by column dialog box

Now, go back to the report and refresh the connection to the model.

Done!

powerpivot model month name are sorted correctly

This is so because by default since the month name is “Text” – it was sorted in A to Z format. But you saw how we can use the sort by column property in PowerPivot model to fix this issue.

That’s about it for the post! your feedback is welcome!

Want to Read More? Here are few links:

SolidQ Journal: Building Ideal PowerPivot Model for Power View reports

Standard

My journal article titled “Building Ideal PowerPivot Model for Power View reports” got published in SolidQ Journal. In this article, I talk about reporting properties in PowerPivot Model that you can set which will enhance the Power View report creation experience of your end-users. Here are the five main topics discussed in the article:

– Hide from Client Tools
– ImageURL
– Default Field Set
– Table Behavior
– Calculate Columns and Calculate measures

In the Part 2 of this series, we will discuss the reporting properties in Tabular Model to help you build an ideal Model for Power View reports. I’ll let you know when that’s published Part 2 is Published: http://parasdoshi.com/2012/09/25/new-journal-article-published-title-building-an-ideal-tabular-model-for-power-view-reports/

I would also like to thank the SolidQ Journal Team and Ruben Lopez who was the Technical reviewer of the article.

And if you have any feedback, please drop a comment or contact me. Thank you