Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0225 Financial Reporting from Trial Balances
I like the way you made the general changes in Transform Sample File query. It's very cool and efficient, much better then cleaning up the grid after combining of files. Thank You, Mike 🤝
Thank you, Mike, as always. I've been working on similar data using Power Query and Dynamic Arrays together, and I found this video quite relevant and helpful.
@@ExcelOffTheGrid I created a file for this a long time ago, Mark but you can take a look at it by all means: just let me know the address to send the file to. What I did was to create a table of data containing all of my accounting entries and by mean of classifying and subclassifying, along the lines of a chart of accounts, I created my pivot table and from there, my trial balance. I know I did it and it wouldn't take much to do it but there is no income statement, balance sheet and cash flow statement in that file. You will appreciate very quickly that I did not use Power Query in those days ... not many of us did! However, with VSTACK(), even this file could do at least some of what you have demonstrated.
Are you referring to a Pivot Table or Power Query? Power Query does not have an undo (so there is no CTRL+Z). You can delete previous steps, but you can't undo.
Hello Mark Great use of PQ , how did you get the balance sheet or TB to pick the latest balance of balance sheet items as those arent running balances like P&L but are as at a period in time.
As the data is Trial Balances, it includes the period end balances for each account for each month. Therefore by using SUMIFS for each period end date it is only summing the closing balances for that one month.
Love your content. Thanks. Instead of "replace nulls with zeros" step, why not use coalesce function ([Debit] ?? 0) - ([Credit] ?? 0)? Same result with no extra step.
Hi, I am looking at your website to pursue this reporting with P&L with monthly and year to date data. Would you have a link to the proper section. Thanks
Regarding the mapping portion starting at 7:45 - I'm doing this same exercise but with my own files (virtually the same as the ones used in the video). I have the acct code in both queries set up as text but when I do the merge, I'm getting almost no matches on acct code across the two files. How can I troubleshoot to see what is causing this error?
Great news that you’re giving it a go. 👍 The issue is probably… 1) The data types have already been converted to numbers in an earlier step, so the leading zeros have already been removed. 2) Spaces around the values. Use the trim transformation to remove them. It’s probably the same issue repeated on several rows. So, you should be able to sample check a few and find out why they are different.
= Table.AddColumn(TB_Sheet, "Custom", each TB_Sheet(Column 1) {2}) Expression.Error: The name 'Column1' wasn't recognized. Make sure it's spelled correctly Would you please tell me what is my mistake ?
I've been playing with polynomials to the nth degree via LINEST for a few years now but I wonder about its efficacy, although I have to confess, I have never done much testing of the results I get.
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0225 Financial Reporting from Trial Balances
i can make by myself...for example center of gravity ...excel cannot make like this..
polynomial excel only 6th degree, i can make nth degree...easy
Very easy to follow. Thanks for the tutorial.
I really like this video! It enlightens me a lot! Thanks soooooo much!
One of best videos which has absolute purpose in everyday's practise.
Thank you man!
Great - I’m glad you liked it!
Brilliant, lots of examples to learn from, and smooth talk. Enjoyed it!
Thanks Rick 😁
i can make by myself...for example center of gravity ...excel cannot make like this..
polynomial excel only 6th degree, i can make nth degree...easy
Awsome Mark
I like the way you made the general changes in Transform Sample File query. It's very cool and efficient, much better then cleaning up the grid after combining of files.
Thank You, Mike
🤝
Yes, definitely use the Transform Sample File - cleaning in the grid would be much tougher.
i can make by myself...for example center of gravity ...excel cannot make like this..
polynomial excel only 6th degree, i can make nth degree...easy
Thank you, Mike, as always. I've been working on similar data using Power Query and Dynamic Arrays together, and I found this video quite relevant and helpful.
Great news - I'm glad it was useful. 😁
Excellent video and practice file. Thank you.
Amazing stuff - I'm glad it was helpful!
Lots of features and functions covered, which is obviously useful. I create accounts from a trial balance using a pivot table!
How do you get the data into the PivotTable? How do you map the report headings?
@@ExcelOffTheGrid I created a file for this a long time ago, Mark but you can take a look at it by all means: just let me know the address to send the file to. What I did was to create a table of data containing all of my accounting entries and by mean of classifying and subclassifying, along the lines of a chart of accounts, I created my pivot table and from there, my trial balance. I know I did it and it wouldn't take much to do it but there is no income statement, balance sheet and cash flow statement in that file. You will appreciate very quickly that I did not use Power Query in those days ... not many of us did! However, with VSTACK(), even this file could do at least some of what you have demonstrated.
Thank you Mark
You’re welcome
This was amazing!! Thanks a lot. Btw, is there a way to go back with some action in pivot table? Like CTRL+Z - seems does not work :D
Are you referring to a Pivot Table or Power Query? Power Query does not have an undo (so there is no CTRL+Z). You can delete previous steps, but you can't undo.
Hello Mark
Great use of PQ , how did you get the balance sheet or TB to pick the latest balance of balance sheet items as those arent running balances like P&L but are as at a period in time.
As the data is Trial Balances, it includes the period end balances for each account for each month. Therefore by using SUMIFS for each period end date it is only summing the closing balances for that one month.
@ExcelOffTheGrid okay, so the sumifs uses month and GL code/name as criteria, correct ?
Yes, correct.
Love your content. Thanks. Instead of "replace nulls with zeros" step, why not use coalesce function ([Debit] ?? 0) - ([Credit] ?? 0)? Same result with no extra step.
Good call on using coalesce - that would be a nice solution. Though probably a bit more advanced than I wanted to go.
Hi, I am looking at your website to pursue this reporting with P&L with monthly and year to date data. Would you have a link to the proper section. Thanks
... is that magic? You have the August 2024 trial balance data before the month is over. 😮
That's right... I'm that good 😂
i can make by myself...for example center of gravity ...excel cannot make like this..
polynomial excel only 6th degree, i can make nth degree...easy
nice
Thanks
How did you underscore the names of the months with tiny spaces between them? Thank you
It's called Single Accounting underline. Go to Format Cells > Font tab > click on the down arrow under Underline > select Single Accounting.
In the Format Cells dialog it is the "Single Accounting" option in the Underline section of the Font tab.
@@ExcelOffTheGrid awesome, thanks!
Regarding the mapping portion starting at 7:45 - I'm doing this same exercise but with my own files (virtually the same as the ones used in the video). I have the acct code in both queries set up as text but when I do the merge, I'm getting almost no matches on acct code across the two files. How can I troubleshoot to see what is causing this error?
Great news that you’re giving it a go. 👍
The issue is probably…
1) The data types have already been converted to numbers in an earlier step, so the leading zeros have already been removed.
2) Spaces around the values. Use the trim transformation to remove them.
It’s probably the same issue repeated on several rows. So, you should be able to sample check a few and find out why they are different.
= Table.AddColumn(TB_Sheet, "Custom", each TB_Sheet(Column 1) {2})
Expression.Error: The name 'Column1' wasn't recognized. Make sure it's spelled correctly
Would you please tell me what is my mistake ?
You need square brackets around [Column1]. Also, it should be Column1, and not Column 1 (i.e. no space).
i can make by myself...for example center of gravity ...excel cannot make like this..
polynomial excel only 6th degree, i can make nth degree...easy
I've been playing with polynomials to the nth degree via LINEST for a few years now but I wonder about its efficacy, although I have to confess, I have never done much testing of the results I get.