BI and Data Analysis

40 Conversations

Latest Activity

Custom List Message Item



How to ADD custom formula for calculation in pivot-table. I'm using "Data Model" in the pivot, See attachment.

11 Replies

Hi Gaurav,


If you add the table to data model you may use Power Pivot for further analysis. However, isn't  included into all Excel versions https://support.office.com/en-us/article/Where-is-Power-Pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b?ui=en-US&rs=en-US&ad=US

... Read More

In Excel 2016 Windows or Mac: has anyone been able to get the FORECAST.ETS.SEASONALITY function to return anything other than zero? I've tried this function on several sets of data, some with very obvious seasons, but this function doesn't recognize them.

... Read More
5 Replies

Hi Bob,


I'm not a specialist in this area, just checked the file which could be downloaded from this support page https://support.office.com/en-US/article/FORECAST-ETS-SEASONALITY-function-32a27a3b-d22f-42ce-8c5d-ef3649269f3c

... Read More

I'm switching over a spreadsheet from using the ATP to using LINEST. Because LINEST doesn't calculate an adjusted R2, my thought was I could just do it manually based on the R2 value LINEST calculates. 


However, the Toolpak Adjusted R2 value does not come

... Read More
0 Reply

Great new feature (preview) is introduced in Apr update of Power BI Desktop. Now we may connect directly to the dataset published in the Power BI service.


Other words – create and support the data model in Excel, and generate visualization in Power BI Des

... Read More
14 Replies
Hi Sergei

Interesting approach, any particular reason you wouldn't create the data model in Power BI desktop? Given you have to load it into there anyway in order to publi... Read More

Hi, i need connect a web page with excel, but give an error (Invalid API Key / Secret Pair. The web page give me to parameters API Key and secret pass. 


Any body can help me??.


0 Reply


On the office 365 plans page a lot of screen space is taken up highlighting (IMHO) fairly low priorty features for small and medium businesses.


The fact that none of these plans contains Power Pivot is much more significant and should be flagged as such.

... Read More
9 Replies

Thank you for letting us know there is some confusion about how to get Power Pivot, and which versions of Office have it. We've put together a help article at https://support.office.com/article/aa64e217-4b6e-410b-8337-20b87e1c2a4b

... Read More
Best Response

They don't contain some other features as well. More exact comparing is here https://technet.microsoft.com/en-us/library/office-applications-service-description.aspx

I gue

... Read More


May 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in


Excel 2016 includes a powerful set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed throug

... Read More
1 Reply

I am used to seeing Analysis Toolpak in the top right corner of the Data tab in Excel.  It has disappeared. It is loaded according to the Options section in File.  How can I get it back?

5 Replies

Hi Steve,

Am assuming you are using Excel 2016.

Anyway, the ATP (Analysis Tool Pack) can be enabled by the following steps:

File>Options>Add-ins........."Manage: Excel Add-i

... Read More

I reappeared today.  I do not understand.

I also have the same problem

Just found what announced few months ago Analyzing Data with Power BI and Power Pivot for Excel (Business Skills) is finally published.


So far had no chance to read it all, but on the first glance it will be another must book for everyone who works with D

... Read More
1 Reply
Up to chapter 5, useful advice so far

For example,

	// comment
	JustTable[a] // one more comment

doesn't work, only

	/* comment */
	JustTable[a] /* one more comment */


Taking into account you practically always within some function single-line comme

... Read More
2 Replies

Received an answer on another forum Microsoft added that as a bug into the system, will fix one day

Is PowerPivot for Excel 2013 32 bit compatible with SQL Server 2014 SP1 Enterprise 64 bit ?

5 Replies

Given that you are using Exel 2013, you have access to the PowerQuery addin, which will definately allow you to get the data you need. My advice is to create an ODBC conn

... Read More
I have some problem to connect PowerPivot for Excel 2013 32 bit and SQL Server 2014 SP1 Enterprise 64 bit, and I wasn't sure if this configuration is OK, because I find o... Read More
Hi Jean-Claude, in what way do you need it to be compatible?

I need to get external data into Excel from a password protected Access database (of which I know the password) which I would like to run Pivots or Power Pivots from. When I go to "Get External Data" within Excel and select "From Access" and select the db

... Read More
1 Reply

Hi Matt,


For my knowledge connection to password protected Access database is not supported by G&T. This link is from Power BI, but they use the same with Excel engine 


... Read More

I am working in Power BI Desktop and I need to be able to access files that are stored on a network share. I'd like to be able to just click on the link and get the file.
I have 2 columns named Folder_Location(UNC path - \\Server\Folder\) and FileName.

... Read More
6 Replies

Hi Guy,


For my knowledge that doesn't work so far. Power BI Desktop (as well as G&T with Power Pivot, same engine) converts the text into the hyperlink if only your text

... Read More

This is only since the most recent Power Query update. I've attached a screenshot of what Power Query displays as well as an attachment of the raw data file being consolidated. Please advise.

13 Replies

Just a follow up to my earlier response.


It looks like the text file is fixed width.  


I pulled it in using the following steps (See attached image and file)




Fixed Width Text File.PNG

Note: I di

... Read More

Hi @Cory Barnes,


I get the same issue as you whether I use the older version of Power Query or the new one.


Can you send a screenshot of the original M code from the Powe

... Read More

Using Microsoft EXCEL. I have a question regarding the ANOVA, two-factor  with replication. I have my data set up such that I have three columns with differnent sample types (labeled above) and 8 rows per sample for two sample groups (labeled on left) and

... Read More
0 Reply