Power Pivot :: Show All Categories Even If There Is No Data

Jul 22, 2015

I have a powerpivot table which groups customers into ranges of sales figures. When I use a slicer to slice by region, I lose some of the ranges because they contain no data. I need them to show up even if there is no data. I've checked all of the boxes to "Show items with no data on columns (and Rows). But this has no effect. I created a measure using the following formula which also has no effect.


View 13 Replies


Power Pivot :: Show Images In Power View With Power Query On Excel Desktop Version / Office 365

Aug 2, 2015

I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:

import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view

Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files 

View 9 Replies View Related

Power Pivot :: Create Power View Report - Button Does Not Show Up For Some Reports

Nov 3, 2015

I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.

View 5 Replies View Related

Power Pivot :: DAX - Show Percentage Of Total?

May 4, 2015

I have 2 columns 1) Total Premium and 2) New-Renew Indicator in my Powerpivot.

The requirement is to show the a) New Premium as a Percent of Total Premium and b) Renew Premium as a percentage of Total Premium. Here is what i did:

a) Created a calculated measure called Percentage:= Divide(Total Premium, Total Premium, 0) . The percentage shows 100% as expected.

b) Now when i try to bring in the Column 2)New-Renew alongside this Percentage in the pivot table, both New and Renew shows 100%. I only have about 20 percent rows with New, and 80% of Renew.

When i bring in the original column = 1) Total Premium, the new-renew split shows correctly, just the percentage is not splitting up correctly. How to achieve it?

View 4 Replies View Related

Power Pivot :: One Slicer To Control Two Pivot Tables That Have Different Source Data And Common Key

Jul 8, 2015

I have two data tables:

1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost

The tables have a common key named obviously as Key. The data looks like this:


I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.

View 5 Replies View Related

Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?

Oct 13, 2015

Can I force the following measure to be visible for all rows in a pivot table?

Sales Special Visibility:=IF(
        VALUES(dimSalesCompanies[SalesCompany]) = "Sales"

FYI, I also have other measures as well in the pivot table that I don't want to affect.

View 3 Replies View Related

Power Pivot :: Auto Refresh Excel Table (Not Pivot Table) Using Data Source

Jul 8, 2015

Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)

View 2 Replies View Related

Power Pivot :: Pivot Table Loses Text Wrapping For Text Data Upon Refresh

Apr 29, 2015

I have a pivot table that connects to our data warehouse via a PowerPivot connection.  The data contains a bunch of comment fields that are each between 250 and 500 characters.  I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.

However, whenever I refresh the data, the text wrapping un-sets itself.  Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text.  This is very burdensome on the user, and degrading the experience.

Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?

View 2 Replies View Related

Power Pivot :: Structural Data Model Changes In Data Source Leads To Errors

Oct 12, 2015

I've question about how to handle structural datamodel changes in a datasource of PowerPivot. Suppose I'm developing a starmodel in SQL Server and sometimes a datatype changes or a name of a field changes in a table. It seems to me that PowerPivot handle this not gracefully as Analysis MD does (mostly). I received an error because of a wrong fieldname or even no error when a dattype changes in PowerPivot. Is this common or do I something wrong here. Does this mean that every time the datamodel changes the PowerPivot should be recreated? Or am I missing the clue here?

View 6 Replies View Related

Power Pivot :: External Access To Data Sets In The Data Catalog?

Apr 23, 2015

I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.

Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?

View 3 Replies View Related

Power Pivot :: Merging Static Excel Data With Dynamic Data?

Aug 19, 2015

I have some data in Excel - sheet1 which would be static.

Now I need to import data from SQL Server (using a query) and Union above static data with this SQL data and later I will have to create PowerPivot table in Sheet2.

Which is suitable option for me to import data from SQL to excel as I see "From SQL Server" option under "Data" and "Power Query" tab as well.

How to merge above SQL data with existing static data?

(SQL Server 2012)

View 6 Replies View Related

Power Pivot :: User Name For Data Connection?

Jun 8, 2015

I am connecting to an Access data source which is password protected. But when I set up the connection in PowerPivot what User name do I need to use for the connection. I have tried various log on user names but non have worked.

View 2 Replies View Related

Power Pivot :: Using Data From Slicers In DAX Calculation

May 13, 2015

I have a formula that should use data from 2 slicers:

Spend per period (changing currencies & dates):=[Spend per period]*CALCULATE([Sum of Value],FILTER(Currency,Currency[Date]=[End Date]),FILTER(Currency,Currency[Attribute]=CurrencySlicer[Attribute]))

I managed to link the [End Date] from the slicer to the formula, however the [Attribute] field is not numeric so I can't duplicate the same methodology. 

{FYI:    End Date:=LASTDATE('Finish Date Slicer'[Column1])    }

I assume that I need to build a formula to extract the data chosen in the slicer, and can't connect it directly to the slicer. 

View 2 Replies View Related

Power Pivot :: Using VBA To Add Or Change Data Connection

Mar 4, 2014

My PowerPivot report is using quite large data that can be split by subsidiary. I’d like to let user select subsidiary. The selection would trigger change of the PowerPivot’s data connection so that the SQL would change into “SELECT

WHERE Subsdiary
= 'selected subsidiary'”.

The Excel 2013 has Model class that should manage the PowerPivot data sources. The object explorer shows CreateModelWorkbookConnection method, but I can't figure out the VBA syntax.

View 5 Replies View Related

Power Pivot :: Dates MTD Function And No Row Data

Oct 20, 2015

I have a DATESMTD function which is not working.  This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero.  See below my formula:

MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])

Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.

So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date  for Sept instead of zero.  What can I do to make the system insert zero in the month to date column instead of the $200.00.  What am I doing wrong in the formula.

View 8 Replies View Related

Power Pivot :: Deleting Data Content From Data Model

Sep 10, 2013

I don't know if the question has been nailed down.  Aside from deleting tables, can we delete the *content* of data within the tables.  It doesn't seem crazy that, if you can pull in data from a feed then you should be able to remove the content out again (without also destroying the user's meta-data work ).  Reasons for this include:

- Security (a user may not have rights to see *my* data and should go refresh their own)
- Size (workbook doesn't need to have GB's of irrelevant data saved to disk in a workbook if it was just useful during development phase to a pre-production data feed)
- Bad data (pre-production data feed is not good data)
- User-friendliness (data feed was refreshed 2 years ago and workbook was saved to file server.  Users shouldn't be presented with irrelevant data, but should get empty pivot tables until they go do their refresh)

Obviously Excel internally knows how to clear out PowerPivot data, given the prompt shown here: [URL] ....

But how does a user initiate this on their own (corruption aside)?

Previous time this question was asked, without a real resolution: [URL] ....

View 8 Replies View Related

Power Pivot :: Comparing YTD Data To Average Of Data In Last 6 Months

Jun 9, 2015

I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following

YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])

Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.

I tried the the DATEADD function instead but got no luck and data came out wrong!


For some reason, this also returns blank in my model:


View 2 Replies View Related

Power Pivot :: Power Query Error Expression / Cannot Convert The Value To Type Text

Jul 20, 2015

I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example

Expression.Error: We cannot convert the value 40 to type Text.

The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.

View 2 Replies View Related

Power Pivot :: IF In Power Query Statement - Converting Text To Numbers

Nov 18, 2015

Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:

#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))

This works fine if you chose a single location.  However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value.   I was therefore surprised when the same query took 2.5 times longer to refresh????

My PQ now looks like this

  #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))

I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?

View 9 Replies View Related

Power Point :: How To Generate Rank Based On Some Group In Power Pivot

Apr 10, 2015

I have data like below

Country     State            Rank
India         Kerala            1
India         Kerala            2
India         Kerala            3
India         Tamil Nadu     1
India         Tamil Nadu     2
India         Orissa            1
India         Orissa            2
US            Florida            1
US            Florida             2
US            NewYork         1

I have to generate rank like this in power pivot. How can I achieve it?

View 3 Replies View Related

Power Pivot :: Meta Data Query Updates

Sep 1, 2015

I have been given a request by a business analyst to update the text 'old' to 'new' within the column names / measure names and associated calculations within a PowerPivot model. There are hundred of columns / measures / calculations, etc. which need to be renamed.

Is there any way of updating these changes to the model other than making these changes manually or is there some way of doing the following type of operation in PowerPivot; -

UPDATE tblColumnNames SET Column_name, etc REPLACE ('old','new', all columns),('old','new', all measures),('old','new', all calculations)

View 2 Replies View Related

Power Pivot :: DAX Formula Copying Data From Column A To B

Oct 1, 2015

I have a problem copying data from column A to column B in Power Pivot.

View 3 Replies View Related

Power Pivot :: How To Change Data Source For Model

Feb 25, 2014

I have an Excel 2013 file with lots of DAX connected to an Azure database. I'd like to reuse all that work by changing the data source for the PowerPivot model to a different database which is an exact copy (just empty) on the same server, but Excel won't let me. In PowerPivot I can change the database connection, the user ID and password as well as the connections name. When opening each table properties (inside PowerPivot model) the new connection is used and all old data is removed, but as soon as I refresh using Existing connections, both from PowerPivot or from the Excel Data tab, the old connection is used and old data is reloaded.

If I use Existing connections from inside PowerPivot, I can se that the new connection is highlighted and has the correct variable, but I think maybe that one is run first, then the old one is run afterwards (or something like that).

On the Excel Data tab, I can see that the old connection is the only one Excel itself seems to know about, but I cannot change anything there as it's read-only.

There must be a way to change this. Even with copy and paste it would take me days to recreate this Excel file from scratch and it would be a serious flaw and reduction of usability for PowerPivot.

View 10 Replies View Related

Power Pivot :: Filter Sharepoint List Data In It?

Mar 20, 2015

I am trying to import the data from SharePoint into my PowerPivot window as a Datafeed. I am able to successfully import the whole data from SharePoint list. But, now i would like to apply a filter (Where Clause) before importing the data.

View 2 Replies View Related

Power Pivot :: Error When Updating From Power Query Source

Jun 11, 2015

When updating a Power Query Source in Power Pivot, I'm getting the following error message:

Basically saying that OLE DB or ODBC-error occured when:

- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel

None of which is the case here. What can cause this?

System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.

View 5 Replies View Related

Power Pivot :: How To Set Refresh For Excel File Uploaded In Power Bi

Sep 9, 2015

I have opened an account in [URL] and taken the 60 days trial for power bi pro. I've developed power pivot and generated power views in share point 2013. But, I'm new to Power BI desktop. I have created a report in power BI desktop and published to [URL]. Also, I have uploaded an excel file directly to [URL] and created the report from the workspace available there itself and pinned the report to dashboard also. Everything is fine till this. But, I need to refresh the file which I have uploaded. I have some dummy data in excel sheet.

ZipCode State ZipName

2345 AA AA
456 BB BB
6787 CC CC

 This has been created as a table and then added to data model. So, power pivot has been created for the same. Then I have uploaded this file to [URL] site. But, I'm getting an error message while trying to schedule refresh for the same.

"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model."

How can I create a data model connection? How can I schedule refresh for an excel file like this?

View 3 Replies View Related

Power Pivot :: Power View - Mix Series Types On Same Chart

Jul 25, 2015

I'm a relative newcomer to Power View. I've been playing with charts and have been struggling to combine both line and bar on the same chart. It would appear this functionality is not available. Considering this is basic functionality when it comes to charting, how to achieve this....

View 3 Replies View Related

Power Pivot :: Power Query Conditional Text Replacement

Jul 16, 2015

I'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the error 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

I plan to change the expression to test for multiple conditions however I need to get the basic expression working first. The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?

FullFilePath = "C:PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16",

[Code] ....

View 8 Replies View Related

Power Pivot :: How To Customize X-axis Of Power BI Dashboard

Aug 26, 2015

I want to show on Power BI Dashboard a moving average - for example, I want to always show the last 30 measurement of body temperature but it looks like Power BI dashboard shows all measurements I have and compress them - which makes the dashboard ugly.

I tried to customize the X-axis properties but I dont know what I should change the default start/stop properties to (where the default property value is automatic).


View 3 Replies View Related

Power Pivot :: Not Showing Data Because It Is Not Clear How These Fields Are Related

Apr 1, 2015

Purpose - relate Server to Software, and Server to App and display it in power view.Current State - I am able to display that Server/Software and Server/App combos separately in power view.  However when I add Server/Software/App it throws the warning as seen below.  This leads me to believe the relationships aren't transitive so to speak.

Supporting Info - v_gs_installedsoftware is the software table.  It relates to v_r_system which is the server table through resource id.  The software table can have many rows with the same resource id, however the server table only has unique resourceids (think of it as a server can have many software products installed).  Table1 is the application table, it relates to the v_r_system table through the resourceid.  Each server from the server table can have many applications that it actually plays a role in (think of a web server that hosts multiple websites for different business units).

View 3 Replies View Related

Power Pivot :: Filter To Slice Data Per Hour / Day Or Month

Mar 31, 2015

I have an Excel database file that contains the total passenger passes from a specific location. The total number of passenger passes is counted in a period of 2 minutes(e.g. 14:45:00 to 14:46:59). I have imported my database into PowerPivot and have also created relevant PivotTables and PivotCharts with some slicers to analyze them. How can I create a slicer which filters data in greater periods of time like hour, day or month?

View 4 Replies View Related

Power Pivot :: Empty Data Model Powerview Report

Oct 5, 2015

I need to create a data model for powerview report but I don't have data right now. The data will be loaded later after I create the report. What technique should I use??

View 2 Replies View Related

Power Pivot :: How To Add Calculated Fields To Every Query In Data Model

Nov 12, 2015

I have created 60 queries and added them to my data model in Power Pivot/Excel 2016. I created some calculated fields for one of my queries but I would LOVE to know how to just copy these across all queries in the data model instead of having to create them in the data model for one query at a time. That will take forever. 

View 3 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved