1. M

    Export data from connection to txt file without loading to tab.

    I have a data connection that contains 3M rows of data. I want to export a subset of that data (total 1.5M rows) to a text file. Can that be natively exported/written to txt file without loading to tab (which obviously is beyond row limitation for a sheet). If it is possible, I would be...
  2. M

    Combining two date cells with measures

    Hi, I'm struggling to get this to work, any solutions would be much appreciated. - data shows forecasted and actual sales for the year. - data has two date cells, 'projected close date' and 'actual close date'. i.e. when we think it's going to land and when it actually landed. - want to create...
  3. H

    Powerpivot: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique"

    Hi, I have a problem getting two tables to work together despite unique relationship. The table 3 has a column (date) which is unique. The Table 1 and 2 have also dates but not unique. When building the relations between these it works fine, but when i excel trying to use these data in a pivot...
  4. H

    DAX to filter values based on other tables

    Hi, I would need advice on how to create pivottable, based on inputs from several tables. 1. First I have an account masterdata table Account Sign Level hierarcy 1010 1 Unit Balance sheet 5610 -1 PG P&L This table defines characteristics of each account, and if the account...
  5. M

    PowerPivot Calculating Measure on semi-aggregated data

    Hi all, This forum has already helped me so much in the past months but now I have the first question I couldn't find an answer to anywhere else, really hope someone can point me into the right direction. I have an Excel model where I use a data model and power pivot. Basically user enter the...
  6. S

    Power Query issue with select dates

    Hi, I would like my POWER QUERY to filter only a select date range. Sales from 730 days ago to 830 days ago. Sales from 365 days ago to 465 days ago. Sales from today to 100 days ago. Here is my current code: SELECT tblsale.sale_link, tblsale.loc_code AS [LOC#]...
  7. GraH

    Excel expert gevraagd met kennis van PowerPivot en PowerView (Brussels Area)

    Beste, Mijn voormalige manager, CEO van Ypto, de IT leverancier van de NMBS, zoekt dringend een excel expert met kennis van PowerPivot en PowerView. Het betreft hier een korte opdracht van ongeveer 1 week. Mogelijks kunnen nieuwe opdrachten volgen. Als je in België woont en interesse zou...
  8. V

    Powerpivot lookup table to two different tables that are themselves connected - best way to model?

    I have three tables. I'd like to work with them using data connections in Powerpivot so I can create simple Pivot tables. The tables are: One is a fact table with the usual customer, product number, sales, gallons, etc. The second is a lookup table which allows me to lookup a "cost center"...
  9. tbablue

    PowerPivot - New measure - calculate based on two criteria

    Hi Forum, Quite straightforward I'm sure - but I'm new to measures in PowerPivot. I have three columns; Definitive Name, Yr.Mnth & Employee FTE. The table name is 'Summary Query'. I need a measure that will calculate Employee FTE based on the number of times that 'Definitive Name' appears in...
  10. B

    Relationship won't work on Power Pivot

    I've attached an image with my problem, of course I did this in separate sheets and only copied it into one so it's easy to visualize. I have in the first table my sales and in the second one every article with its brand. I want to know, in each month, how many different brands were sold, but it...
  11. W

    Power Pivot - aggregate within groups to determine max value

    I'm looking for a formula (for Power Pivot) that aggregates within certain groups and across other groups to determine the maximum. Here's my data table: State Customer Fruit Qty NY A Apple 5 NY A Orange 1 NY A Pear 5 NY B Apple 1 NY B Orange 6 NY C Apple 2 NY C Orange 2 NY C...
  12. P

    Power Pivot - calculated column in Pivot Table

    I tried to have calculations of difference between two months in two years in the pivot table from power pivot. I've done some research but couldn't find a way to solve my purpose. Unfortunately, the Power BI is not available only Power Pivot and Power Query. I remember previously in Pivot...
  13. B

    Stuck on analysing support cases (average/high/Low) pivot chart

    So i'm stuck on grating a pivot chart / chart for showing average / high / low trends on time from creation of a supportcase until it is closes. What i would like to have in a chart is how average hours from creation to resolved changes over time. I'm totally stuck on this, so any input is...
  14. C

    Row Header Format is Bold when no children exist

    I am using a calculated column showing the level of an account and several measures to allow children to show only when necessary. However I noticed one thing - Excel Power Pivot treats all row labels within a group as row headers (bold) even though not all have children. In my mind this would...
  15. M

    PowerPivot report - getting multiple filter values and calculating prior year data

    Thanks in advance! I'm creating a PowerPivot based report in Excel 365. I'm reporting actual quarterly sales data, performance against plan, and performance against prior year. There is a calendar table that links to the data table. I'm deriving prior year quarter by taking the value of the...
  16. G

    PowerPivot - Granularity issues with a many-to-many relationship

    I have two fact tables, one that is a list of stays at multiple hotels with 4 columns (Location, arrival date, departure date, member ID), another that's a series of transactions with a bunch of details (location, transaction date, member id, item sold, item category, etc). Both tables have the...
  17. R

    Excel Power Pivot with 2 measures in Value column

    I have an excel Power Pivot, using measures. Here is what the current output looks like. The server column is the result of a measure using a concatenatex function: Note that the Servers exist within a Group, and that each App defined in a Group are all on the same set of Servers. To avoid...
  18. M

    Power Pivot - Change Power Pivot Connection to Power Query Connection

    Hi lovely People! I'm trying to update my power pivot data model to keep a table with my measures but change a Power Pivot Data Connection to a Power Query Data Connection. Using Power Pivot Data Model Connections in my WorkbookDataModel.xlsx This is my table: When trying to edit to get...
  19. G

    PowerPivot CALCULATE SUM

    Hi, would appreciate any help with this, I'm trying to do a calculate sum if in PowerPivot, example of how it workls in Excel below, but am unsure how to replicate in PowerPivot. Thanks in advance for any help. Gav.
  20. G

    PowerPivot Count Duplicates

    Hi, would appreciate any help with this... I have a table of Invoice Data, Including Company Number and Date, I need to count the number of duplicates where a company has more than one invoice for the same day. ( I need to count in PowerPivot itself, as I will be performing more calculations...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back