Home

BI and Data Analysis

50 Conversations

Latest Activity

Custom List Message Item

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?

210 Views
6 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

Good afternoon,

I am using Excel 2013. I have added in the Data Analysis add-on (and the VBA one). The data analysis button shows up on the right of my toolbar, but when I click it nothing happens..the box with the options of which test to run does not pop

... Read More
39 Views
0 Reply

Really need help and as soon as possible!

 

Have a data range that is based on each other, and want to change one of the results in a cell and make excel count backward. To an analysis an increase/decrease. 

 

Help please!

Read More
67 Views
1 Reply

Hello,

your question is not very clear. If you want us to suggest a formula, it would help immensely to see the data structure. You can upload a sample file or a screensho

... Read More

Is there a way to set the sorting default to sort "largest to smallest"? Every time I sort it makes me hit the pull down menu because the default seems to be "smallest to largest".

 

Thank you.

56 Views
2 Replies

Hello,

 

AFAIK, there is no way of changing the default sort order. However, the data ribbon shows the AZ command and also the ZA command, so you can sort ascending or desc

... Read More

I have collected data on 4-5 types of indicator in a single excel sheet for 100 organisation. now I want to grade each organisation and give a rank according to this data.  pl suggest

47 Views
1 Reply

hi @Md Ehteshamuddin,

 

Can you explain more.

Hello

I have a table in powerpivot data model two tables linked to each other through a one to many relationship. the link is dne through the key "Role"

In the one side table I have put two colums the first one is the role ( role of a sales representative)

... Read More
242 Views
23 Replies
Hi Imed

When you say you can't implement this do you mean it won't let you join the 2 tables together?

Hello,

I am trying to load a larger list >40K records with a few lookups into Power Query. It works for smaller lists, but fails with following error msg on large lists.

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500

... Read More
73 Views
1 Reply

Is there any way in DAX to identify were a Dimension Filter has been place in the Pivot Frame?

 

The Issue is I am Trying to "Default" a Filter Selection Via Dax . ie You have a Filter True/False.

I want my Pivot Table to show True Data UNLESS the User Expli

... Read More
90 Views
5 Replies

David,

 

Could you put a slicer in conjunction with  IF HASONEFILTER 

 

 

Measure1

=IF (
    HASONEFILTER ( [Boolean] ),
    SUM ( [Cost] ),
    CALCULATE ( SUM ( [Cost] ), Dat
... Read More

Hi David,

 

Here is quite old article with workaround http://spr.com/setting-default-member-using-tabular-mode-ssas-excel-2013/. On the othre hand I don't think something c

... 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??.

 

75 Views
1 Reply
Any body can help me please

Hello, I need some help regarding Power Pivot. First of all, I do have Office 365 Home Edition. Does this version support it or not? If not, which versions support it. Also, what are the steps to enable the add-on and import database file to work on it in

... Read More
92 Views
1 Reply

Hi,

 

Nope, Power Pivot is not available for Home edition, which versions are supported is here https://support.office.com/en-us/article/Where-is-Power-Pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b

... Read More

Hello,

 

Need help in calculating Horizontal calculation in Power BI.

 

I have data shown in image "ABC", categorized by Months, and Sales amount is filled against Executive ID, in frequency column, I need the count of sales in frequency column (which reflect

... Read More
81 Views
3 Replies
Hello Sergei, Thanks for reply, i believe it will solve my problem. Is there us any other DAX expression to solve this problem, like "horizontal count/sum"??

Hi Gaurav,

 

If the task is to calculate the number of not empty cells in each row, when i use this code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
... Read More

Hi,

 

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

170 Views
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
145 Views
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
49 Views
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
547 Views
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

 

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
370 Views
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
86 Views
1 Reply

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
136 Views
1 Reply
Up to chapter 5, useful advice so far

For example,

JustMeausre:=SUM(
	// comment
	JustTable[a] // one more comment
)

doesn't work, only

JustMeausre:=SUM(
	/* comment */
	JustTable[a] /* one more comment */
)

instead. 

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

... Read More
162 Views
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 ?

148 Views
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
126 Views
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 

http://community.powerbi.com/t5/Desktop/How-do-I-connect-to-a-password-protected-Access-Database-Is-this/m-p/143447#M61889

... 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.
 
I

... Read More
190 Views
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.

311 Views
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
164 Views
0 Reply