Excel 2010 :: How To Get Power-query And Power-pivot
Jun 28, 2014How to get Power-query and Power-pivot for Excel 2010?
View 9 Replies
ADVERTISEMENT
Excel 2013 :: Power Pivot Summarize Value By Category From Total?
Apr 17, 2014Using Excel 2013.
Trying to create a Calculated Column in a table to put Expense in 1 of 4 categories
I came up with =CALCULATE(sum([TotalExpense]),FILTER(factExpense,factExpense[ExpnseType]="Sundries"))
But that is returning total instead of a total for each record What did I miss?
This works for the first Calculated column: =CALCULATE(sum([TotalExpense]),FILTER(dimExpense,dimExpense[Type]="Sundries"))
But as soon as I copy the formula to the next column and update the type to "Wines_Spirits" I get
A circular dependency was detected
Found the answer on SQLBI website I added a unique id to each row and in Table Behavior in the Data Model set the Unique_ID as the Row_Identifier
[URL]
Excel 2013 :: Power Pivot DistinctCount If Another Field In Table Is Greater Than Zero
Apr 21, 2014Using Excel 2013.
I am trying to get DistinctCount from a field if another field in the same table is greater than zero.
PowerPivot DAX -COUNTIF
[Code] .....
The count returned includes all not just values greater than zero. How can I get just values greater than zero?
Using Power Query To Do Multiple Searches On Website?
Jul 10, 2014I've been trying to get power query to search a list of parcel numbers on an assessor site: [URL]. The problem I'm getting, every time you put in a different parcel number, you get the same URL for the results. I want to extract just a few pieces of information for each parcel search, but I can't initiate a search through excel. I'm thinking the problem is in the Data source settings. How can just get it to show me the assessors page results for a single parcel search in excel?
View 5 Replies View RelatedPower Pivot Chart With Stacked Column
Jul 30, 2014I have created a Power Pivot Chart with Stacked Column chart. Made one axis as Secondary Axis and the axis is Clustered Column, but it is overlapped with Stacked bars.
Is there any easy way to solve this issue, apart from creating 2 dummy columns and making it work.
Create Report Using Power Pivot While Creating Relationship Between Linked Table?
Apr 24, 2014I want to create a report using power pivot while creating the relationship between the linked tables, power pivot is throwing error "The relationship cannot be created because each cilumn contains duplicate values. Select at least one column that contains only values"
View 1 Replies View RelatedForcing Excel To Show Numbers In Scientific Notation To A Constant Power?
Mar 8, 2013I need to have all of my number be "x10^-6", but Excel wants to make them, for instance, 7.66x10^-3, when I need it to read "7660x10^-6". How can I force excel to do this?
View 3 Replies View RelatedTan To The Power Of Minus 1
Dec 10, 2008How can i produce "tan to -1 " in excel ?
View 9 Replies View RelatedMore Sheet Power
Jun 27, 2006im building a fairly intricate workbook at the minute but it is starting to slow down significantly lately as ive added more fromula's and sheets. I was hoping to make it even bigger but it looks like i might be stoped in my tracks. I have a reasonaby powerful computer and excel 2003. Does anyone know a way i could increase the capacity of this one particular workbook as it will be used by many people to enter data daily.
View 3 Replies View RelatedTo Find The Power ( I.e. ^ N) When You Have Other Variables
Mar 12, 2009To find the power ( i.e. ^ n) when you have other variables.
Mathematical Perspective as well as through EXCEL..
Ex: 5^n = 125
We know that 5^3 = 125,
Now if I have been given the value 5 and 125 , then how do i get the answer 3 ( i.e ^ n) value...
I need to solve a slightly complex issue and therefore need help for the above requirement as well as to get the "n" value..
Nested IF Formula (If Value In Cell R5 >0 Then Get The Value In Cel X29 On Power 4)
Jan 12, 2010I am trying to do a nested if formula but I can't get it to work.
The condition is: If value in cell R5 >0 then get the value in cel X29 on power 4.
=IF(R5>0,POWER(X29,4)
Type Numbers To The Power Of Number?
Jul 14, 2002how can I type numbers to the power of something Say 12 to the power of 3. I want the 3 to be smaller and up off the line is that possible
View 3 Replies View RelatedMathematics - Power Trend Lines?
Jan 23, 2014I am trying to fit a cooling graph with a trendline and I have found the power line to fit the best, as well as corresponding with Newton's law of cooling. However, I am not savvy enough to discuss how similar or dissimilar the two equations are!
the resulting equations from two different curves are as follows:
y=4305 x^-0.87733
y=4025 x^-0.91004
both fits have an R^2 of 0.996.
I am analyzing two scenarios of cooling the same object with three variables, one intended (the extraction of energy using water flow in one case, and no extraction in the other) and the others not under my control: different starting temperatures and different ambient temperatures. I need to understand if these are generally the same curve, or how very different are they? is there any statistical way to represent their relationship?
Copy Tables Into Power Point
Apr 25, 2008I have a question about coping table from exel into ppt, is this posible or not,
PS: i just need to copy a specific range of cells ex. A1 g17 into a slide in pp.
Copying Selections To Power Point With VBA
Feb 23, 2009I am trying to write a routine (via two comboboxes) that will enable users to select a choice of charts from one of three possible worksheets and have the charts on the selected sheet copied to PowerPoint. My difficulties are these:
a. I cannot determine how to pass the selections to Excel (see b. as well)
b. In some instances my charts are actually groups of a chart and a picture. how I should reference these? They are obviously not ChartObjects: when I use the macro recorder all I get is a range.
Combination Of Pairs, Power And String
Aug 8, 2006I have a string of n pairs and want to check various combination of that string.
Example: Pairs 58 78 15
Since I know I have 3 pairs (but it can be 2 or 4), I know the number of combination I want to test, ie 2 power 3 = 8 combinations. How can I program a code creating the various strings, ie 587815, 587851, 588715, 588751, 857815, 857851, 858715, 858751 ?
This is what I have so far (not much):
Public unique_pair 'number of pairs provided by another macro
Public mystring 'provided by another macro
Sub make_guess()
Dim number_of_combination, i
number_of_combination = 0
number_of_combination = 2 ^ unique_pair
For k = 1 To number_of_combination
'how to generate the various string ????
Next k
End Sub
Excel 2010 :: Pivot Table Filter Query
Feb 21, 2012I have created a pivot table in Excel 2010 that lists amounts used of particular items each month. Inside the pivot table value field settings i have changed the show values as field so that it displays the difference in usage from this month to last.
However if i now select a top 10 filter it only filters by the total number used as opposed to the difference in usage from one month to the next (which is the values displayed).
Is there a way that i can filter by top 10 by the actual values displayed in the pivot chart and not just the underlying data that creates it?
Selected Range To Power Point Kinda...
Oct 7, 2008I need to be able to send my selected range of cells (to include: the spreadsheet background, colored cells and all values contained) to Power Point for a daily product. I found this great VBA online and would like to use it but the problem is I keep getting funny results. It doesn't matter what cells I select I keep getting a extra margin on the left side and top of the pasted product in power point. I then have to crop the results... I have looked at the macro (from my noodie eyes) and cannot seem to find a way to adjust this added on margin.
View 5 Replies View RelatedVBA To Save And Close Power Point Presenation
Oct 16, 2009I am using the following code to Open a presentation, and run a macro. Everything works fine, but I would like it also to Save the presentation and Close it.
View 7 Replies View RelatedSuspend/hibernate Sheet To Free Cpu Power
Apr 28, 2007how to suspend/hibernate some specific sheets in one workbook , to free cpu power?
View 5 Replies View RelatedFill Chess Board Matrix To Power Of 2
Jun 3, 2007i would like to write vba to fill a "chess Board" Matrix (8X8) With Powers Of 2...
starting with 2^0 and ending with 2^63...
(i suppose, click a button/icon and have the numbers fill in/populate...)
Lower Screens Brightness To Save Power When Desired
Feb 21, 2012Have a system that runs 8 monitors on one computer. 4 are very large screens. Creates a lot of heat and uses much power.
Is there a way to dim the screens brightness in VBA. Looked all over and can't find any code.
Not really looking for a screensaver as they can cause interrup problems etc if I have programs running for hours. And I can't just turn off some screens as then desktop icons placements get scrambled.
Just want to dim screens with a macro, then undim it. Don't need to have it linked to the mouse movement, but that might be nice too.
I tried a macro that said "Dim MyScreens" but nothing happened.
VBA Code To Configure Windows Power Management Settings
Jun 25, 2009Is it possible to modify the windows power settings in excel vba. I have this example, but it doesn't do anything.
Sub testPOWERCFG()
CreateObject("WScript.Shell").Run "POWERCFG /CREATE Custom1", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-ac 15", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-dc 10", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /SETACTIVE Custom1", 0, False
End Sub
I need some code to create and activate a scheme whereby everything is on and also some code to revert back to the default settings on the machine.
Excel 2010 :: Web Query Using Changing Filename
Mar 6, 2014I'm using excel 2010, looking to import a .xls file to a tab from a web query. Herein lies the problem: the .xls file is named based on the current date. So for instance, xLfile03.05.14.xls. Which changes the next day to xLfile03.06.14.xls. This is not a local file but one on microsoft sharepoint. It's ONLY available through sharepoint.
View 1 Replies View RelatedExcel 2010 :: Workbook With Two Sheets / Query Parameters
Mar 6, 2012I have a workbook that contains two sheets with two separate queries. On the first sheet I have two cells designated to accept the values for the parameters. The first sheet accepts the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.
View 9 Replies View RelatedExcel 2010 :: (VBA) Pivot Table And Pivot Cache?
Mar 13, 2014My macro is designed to look at a summary source tab and create a new tab for each unique project number. It then creates a pivot table from five different source detail tabs and filters on the project number. If a tab already exists it selects the tab and moves on to the next project number. There are six pivot tables created for every project.
New data is added each month to the source tabs and I have a macro to delete all pivot tables and the macro will recreate the pivot tables when ran again.
Issue: Running out of resources At work I'm limited to the use of Excel 2010 (32bit) so I'm restricted on 2GB of memory. At home I ran the file successfully (64bit) and it was around 3GB of memory.
My macro creates a new pivot cache for every pivot table where as I'm trying to only use 6 pivot caches in my coding. I kill it half way through and it's around 100+ caches causing unnecessary usage of memory.
Fix / Solution:
Correctly code the vba to only create six caches and code the rest the pivot tables to use that cache.The only difference in the Pivot Tables is that it’s sorted on the Project Number.
Code:
Dim VBAPPPC As PivotCache
Dim VBAAPPC As PivotCache
Dim VBAPRPC As PivotCache
Dim VBAEXPC As PivotCache
Dim VBAMJPC As PivotCache
Dim VBAIVPC As PivotCache
Dim VBAPT As PivotTable
[code]...
Excel 2010 :: Assigning Name To Range Received By ODBC Query
Apr 29, 2014I have been working on some ODBC queries in Excel 2010.
First, I recorded them using Excel's Record Macro function, to get an idea of what sort of source data I would need. Then, I rewrote them into something a little more intelligible.
Pulling out all the sensitive info, I want to know how to add the .ListObject.DisplayName property back onto the results of the query. I tried doing it intuitively, but it didn't work. It was in there when I recorded the macro, but I can't seem to figure out where to put it back into the re-written code. Other parts of the code depend on the results of the query being a 'named field'.
[Code] .....
Also, where would I find out what all that stuff in Cnnect means? DBA, APA, EXC, FEN, etc are all just assignments, and I might like to change some to make this run a bit quicker, if I knew what they meant.
Excel 2010 :: ADO Recordset Query Quandary Locating Duplicates
Mar 13, 2013I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references.
So far, I have chopped the query down to it's most basic elements and reassembled until it broke. Necessary pieces work well, but when I combine them, I get nowhere.
Here is the working simple query to retrieve a single row:
Code:
dupeSQL = "SELECT PSID FROM [Obstructed$] "
Here is the working single query to retrieve a count:
Code:
dupeSQL = "SELECT COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "
When combined, I cannot figure out why it won't work:
Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "
Here is what I am trying to get to:
Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] " _
& "WHERE COUNT(PSID) > 1 " _
& "GROUP BY PSID"
I can even work without the GROUP BY. I have also played with the HAVING clause to try and accommodate the [PSID] field, but have gotten nowhere.
Excel 2010 :: SQL Query DB - Error - Could Not Find Installable ISAM
Jan 19, 2014I am trying to perform an SQL query on an Excel worksheet, as per one of the examples in Michael Alexander's book "Excel and Access Integration: With Microsoft Office 2007" and I am encountering an error message.
Could not find installable ISAM
To clear I am trying to pull data from one Excel workbook to another using SQL.
I am using Excel Excel 2010 64 bit.
This is the code I am running.
Code:
Sub GetData_From_Excel_Sheet()
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
[Code]....
I have some across a few times that relates specifically to Excel 2010 64 bit is that I need to install the following driver
Microsoft Access Database Engine 2010 Redistributable
However I already have this driver (I installed when I was trying to figure out how to write from Excel to Access).
Why am I receiving this message. It occurs when my code reaches this line:
Code:
MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
Excel 2010 :: Pivot Table Reference Is Not Valid When Moving Data And Pivot Table Together?
Mar 19, 2013On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.