Search:

Type: Posts; User: pjmorris; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.05 seconds.

  1. Re: Power Query - Remove duplicates from Folder Download

    Hi Matt,

    I believe I've found the solution:

    1. Group by Download File, Date, Payee and Amount - this collects any duplicate records on that day into a table.
    2. Select Date, Payee and Amount...
  2. Re: Power Query - Remove duplicates from Folder Download

    Hi Matt,

    Many thanks for this. I have a couple of queries:

    1. I can follow the logic, just not sure what you mean by 'branch the query'? Do you mean 'Reference'? (that is what I've assumed....
  3. Power Query - Remove duplicates from Folder Download

    Hi,

    I'm creating a personal account analysis system and using the 'From File, From Folder' process in Power Query. The issue I'm having is to identify where two downloads from a bank or credit...
  4. Replies
    0
    Views
    355

    What is the correct measure?

    I'll be grateful for any assistance, I feel that I'm close to a solution, but need help in properly defining the measure [EffortAssigned] - either that or I'm trying to do something that is not...
  5. Re: Replace Sumifs Function with Pivot Table in Power Pivot

    Hi, the calendar table has specific characteristics within PowerPivot. I don't have the competence (I'm also learning) to explain here (or the space), but if you look at the PowerPivot book I...
  6. Re: How to use a measure in another table with many to many relationship

    Apologies – I misunderstood what was happening!


    After further investigation I can see that the tblResource is being filtered by the date. It is not being filtered by [Booking Group]. Ive now...
  7. How to use a measure in another table with many to many relationship

    Hi,


    I have two tables:


    1. a Task Table: 'tblTask' containing [title], [sub title], [booking group] and [effort]. the first three fields are text strings and the fourth is a percentage.
    ...
  8. Re: Replace Sumifs Function with Pivot Table in Power Pivot

    maybe I've missed the point, but if you have dates as your column headings then a measure defined as SUM(Table1[payment]) would automatically filter the data.

    You might need to create a Calendar...
  9. Replies
    5
    Views
    551

    Re: How to Add Correct Grand Total for Rows

    Hi, Apologies for the delay in replying - but its take me this long to create some sample data and feel confident that I've asked the right question.

    Background: I will have two tables. the...
  10. Replies
    5
    Views
    551

    Re: How to Add Correct Grand Total for Rows

    Hi, I'm sure I'm missing something easy, but however I define the measure how do I make it appear only once for each row despite there being multiple columns?
  11. Re: Excel 2016 Power Query "Conditional Column" missing from Edit Query ribbon

    Have you tried opening a blank workbook (which is what I did), then select new query, blank query. That is how I identified where it should be. If its missing then I would suggest a reinstall of...
  12. Re: Excel 2016 Power Query "Conditional Column" missing from Edit Query ribbon

    I found it in 'General' group on the 'Add Column' tab, above the 'Index' and 'Duplicate' column buttons.

    HTH.

    Regards

    Peter
  13. Replies
    5
    Views
    551

    How to Add Correct Grand Total for Rows

    <tbody>

    Col1


    Col2


    Col3
  14. Replies
    5
    Views
    479

    Re: Power Pivot query

    Hi,

    Just thought I'd share my adaptation of the query which resulted in the month view I think I need:



    let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
  15. Replies
    5
    Views
    479

    Re: Power Pivot query

    Hi, I've been on this forum for ages and this has to be the most useful help I've received. Thank you so much.

    Really appreciated.

    Regards
  16. Replies
    5
    Views
    479

    Re: Power Pivot query

    Hi

    Many thanks for the reply. I'll follow up with the Calendar link. The challenge will be changing the source data as suggested - I can see why, but obviously need to automate that step as...
  17. Replies
    5
    Views
    479

    Power Pivot query

    I'm learning how to use the power pivot - which is an extraordinary improvement on normal pivottables. But I don't know the full capabilities. My challenge is this:

    I have data arranged as...
  18. Replies
    13
    Views
    563

    Re: Cell-colouring macro

    hi daba

    If I've interpreted your query correctly this should work. If it doesn't, try and understand the code as both Peter and I have given you the core requirements; if you have any queries...
  19. Replies
    13
    Views
    563

    Re: Cell-colouring macro

    Hi Peter,

    You are, of course, entirely correct. I did think of checking the column (IF NOT INTERSECT etc), but it was late here (NZ) and I thought that getting the principal across would help the...
  20. Replies
    2
    Views
    200

    Re: How to combine datasets

    hi Alice,

    I tried that by creating named tables, then joining them using the relationships manager in the data ribbon, followed by using a connection in the Pivotable dialogue. All seems to work,...
  21. Replies
    13
    Views
    563

    Re: Cell-colouring macro

    hi daba,

    This should do the trick:


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nShft As Integer 'used to align the groups of cells
    Const rSt = 2 'first data row
    ...
  22. Replies
    2
    Views
    200

    How to combine datasets

    I have three data sources that I need to consolidate into a single data source:

    1. A list of maintenance periods which are uniquely (no repeats) identified by 4 letters and 3 digits, for example...
  23. Re: How do I format a user defined function output as short date?

    hi, I think you would need to define the function as Public Function Days90() as String

    That said, if you did do that the date would be useless elsewhere in your spreadsheet, you would be better...
  24. Replies
    0
    Views
    108

    Pivotable not Updating

    Using VBA:

    My spreadsheet inserts the formula to copy data from another spreadsheet that will have been selected by the user and could be in a normal file system or sharepoint. I don't know how...
  25. Re: conditionalk format to highlight duplicate numbers only

    delighted to have helped and thanks for the feedback.
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4