PowerPivot Changing Data Type

[cmcreynolds]

Hello -

Built a table on a worksheet and added it to the Data Model but every time I hit "Refresh All" Power Pivot changes my date column to text, which causes an error with my lookups to my calendar table.

I think one solution is to run that table through PowerQuery, first, but is that really necessary?

Is this because there are blanks and nulls in my table? (I'm creating a budget and haven't put in all dates, yet)

I can change the type todate in PP, but when I refresh, it goes back to "text".

Kinda frustrating.

 

[Matt Allington]

I have never heard of this before. I agree with your assumption that it is probably related to the blanks in the data set. I would therefore assume that power query can solve the problem. You can simply change the data type in power query, and my assumption is that power query will handle the spaces and nulls prior to load

 

[cmcreynolds]

Okay - out of curiosity, does running it through PQ "cost" file size or anything?

 

[SimonNU]

Odd, this sounds similar to an old SSAS Tabular bug. I don't think I've ever seen it in Power Pivot... If your data is a SQL query, try sorting it so dates (and not blanks) appear first.

Basically, when the engine is sampling the data (in SSAS Tabular, at least) it uses the first few thousand rows to determine the data type. Sometimes it's very stubborn about what a field should be and it won't let you easily change it to the correct type.

** EDIT, I should add that re-sorting the data sometimes won't fix the model. Yes, it's THAT stubborn. But in SSAS Tabular it will prevent it from screwing up again if you make the model from scratch.

 

[SimonNU]

Always go through PQ if given the option. It shouldn't cost file size.

Although, on that note, I would suggest sorting your PQ query on whichever column has the highest cardinality as this may increase compression if your data is millions of records long.

 

[Matt Allington]

Okay - out of curiosity, does running it through PQ "cost" file size or anything?

No cost on size. Maybe a slight impact on speed, but not significant.

 

[cmcreynolds]

Okay, here's a question that might warrant a separate thread: So, I sent it to the DataModel, then built a pivot table and added conditional formatting and whatnot. If I decide I have to go through PQ, do I have to rebuild my pivot table and formatting, or is there a "sneaky" way of getting the new table into what I had formatted? (I know, I'm being lazy)

 

[Matt Allington]

If I decide I have to go through PQ, do I have to rebuild my pivot table and formatting?

Unfortunately once you change the data source type, you can't change unless you remove the table and add it again. Here are my tips and suggestions.
1. create a temp table and move all the measures into that table. THis will keep the formatting etc
2. you can try to turn off pivot table refresh if you like (Excel 2013). This MAY POSSIBLY stop the pivot updating until after the maintenance. It is worth a try. Install the addin, right click on any pivot and disable auto refresh. Do the maintenance and then turn it back on the same way. https://olappivottableextend.codeplex.com/wikipage?title=Excel 2013
3. remove the table, add the replacement table, rejoin the tables
4. move the measures back
5. turn on refresh if you turned it off.