+ Reply to Thread
Results 1 to 7 of 7

Account balances using power query

  1. 11-22-2020, 05:21 AM #1
    sunboy
    sunboy is offline
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    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
    Reply With QuoteRegister To Reply
  2. 11-22-2020, 09:16 AM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    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
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 11-22-2020, 10:03 AM #3
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Account balances using power query

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  4. 11-23-2020, 09:39 AM #4
    sunboy
    sunboy is offline
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    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
    Reply With QuoteRegister To Reply
  5. 11-23-2020, 10:53 AM #5
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    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.
    Reply With QuoteRegister To Reply
  6. 11-23-2020, 12:00 PM #6
    sunboy
    sunboy is offline
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    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
    Reply With QuoteRegister To Reply
  7. 11-23-2020, 12:24 PM #7
    sunboy
    sunboy is offline
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Account balances using power query

    Hi Alansidam

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

    Regards,


    Sunboy
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. [SOLVED] How to add an Excel formula code into Power Query or create a new one in Power Query
    By afgi in forum Excel Charting & Pivots
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1