+ Reply to Thread
Results 1 to 7 of 7

Account balances using power query

  1. #1
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    excel 2010
    Posts
    126

    Account balances using power query

    Hi

    I am kindly asking for help to produce Account balances using information from Payment and Receipt sheet.
    The solution should be able to capture about 5500 records.

    Find the attached excel sheet.

    Regards,

    Sunboy
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,989

    Re: Account balances using power query

    If this presentation works for you then look at the workbook attached.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Customer Name Date Invoice Amount Incl VAT Receipt.Date Receipt.Receipt Receipt.Amount Balance
    2
    John
    1/10/2020
    C0020
    1145
    1/12/2020
    R100
    1145
    0
    3
    John
    1/12/2020
    C0023
    1030
    1/19/2020
    R103
    700
    330
    4
    John
    1/14/2020
    C0025
    1050
    0
    1050
    5
    Mary
    1/10/2020
    C0021
    1010
    1/13/2020
    R101
    900
    110
    6
    Mary
    1/14/2020
    C0024
    1040
    1/20/2020
    R104
    1040
    0
    7
    Henry
    1/12/2020
    C0022
    1020
    0
    1020
    Sheet: Sheet1
    Attached Files Attached Files

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,932

    Re: Account balances using power query

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    excel 2010
    Posts
    126

    Re: Account balances using power query

    Hi Roy_Ry and Alansidam

    My apologies for late replying, I was offline. There was network problems in my area. Thank you for helping me. Roy_Ry's presentation works for me, but when I add more payments and receipts sheet ALL is not updating. How do I make it up date?

    Regards,

    S. Moyo

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,989

    Re: Account balances using power query

    After loading new data to the source, be sure to click on the Refresh All Icon on the Data Tab.

  6. #6
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    excel 2010
    Posts
    126

    Re: Account balances using power query

    Hi Alansidam

    Thanks, I have done that but it has been a while it is still saying updating in the background.

    Regards,

    Sunboy

  7. #7
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    excel 2010
    Posts
    126

    Re: Account balances using power query

    Hi Alansidam

    It has finished updating its working well. Thanks a lot.

    Regards,


    Sunboy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using Power Query to summarise stock balances at all locations
    By josephteh in forum Excel General
    Replies: 2
    Last Post: 07-23-2020, 03:52 AM
  2. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. [SOLVED] How do you show all account balances from a single ledger sheet?
    By u3rick in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-29-2015, 01:46 PM
  5. [SOLVED] Need help totaling account balances.
    By silentscribbles in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-05-2012, 08:44 AM
  6. Showing Account Balances Nearest to the End of a Month
    By thomask in forum Excel General
    Replies: 2
    Last Post: 06-05-2010, 02:48 AM
  7. Need 3 functions for account balances
    By KMC in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-21-2008, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1