Hey guys,
I need to understand the rules above
I want all the credit values for:
if [Debit] > 0 and Text.StartsWith([AccountID], “2781”) or Text.StartsWith([AccountID], “2782”) and [Grp 03 Acc] = “232” then “i will have all the credits records for all those 3 accounts”.
I don’t think this is true, but i’m not sure that’s why i need to understand those rules.
Can somebody help me?
Tanks
Pedro
If I correctly understand your query then you want to understand how the logical operator works. As these are logical operator so as per my understanding it will evaluate from left to right.
To simplify lets just do all the comparison first and get true or false and then apply logical operation.
[Debit] > 0 — and the result will be true1 or false1Text.StartsWith([AccountID], “2781”) — and the result will be true2 or false2Text.StartsWith([AccountID], “2782”) — and the result will be true3 or false3[Grp 03 Acc] = “232” — and the result will be true4 or false4So now your expression will reduce to as below (assume all condition meets true)
if true1 and true2 or true3 and true4 then “i will have all the credits records for all those 3 accounts”
Now it will compare first and operator which is in between true1 and true2 it will result in true12. Now rewriting the expression
if true12 or true3 and true4 then “i will have all the credits records for all those 3 accounts”
Now it will compare or operator which is in between true12 and true3 it will result in true123. Now rewriting the expression
if true123 and true4 then “i will have all the credits records for all those 3 accounts”
Now it will compare and operator which is in between true123 and true4 it will result in true1234. Now rewriting the expression
if true1234 then “i will have all the credits records for all those 3 accounts”
And finally the output will be — “i will have all the credits records for all those 3 accounts”
Hope this explanation is ok …
Thanks
Mukesh
Hey @MK3010
Well…it was a litle confusing at the first time but then i got the idea (i guess) and i believe you forgot that i want all those records from those 3 accounts that have credit > 0.
How about you write the m code…maybe i could understand better that way, and after i test it here. Is this ok with you?
Thanks
Pedro
Sure will do that could you please help to share sample file.
Hi @pedroccamara,
I just want to give you a heads up on something you might not be aware of. Any general ledger account can have a debit or credit balance at any time. Cash account ending balance can be a credit that means that your company has over drawn there cash account. ie Accumulated depreciation on assets are credits
You could run into issues if you don’t know what account should be assets, liabilities, capital, revenue cogs, expense meaning related to debit or credit ending balances.
I just want to give you heads up on accounts.
thanks
keith
Hey @Keith
Yes, i’m trying to do a balance sheet over my transactions table instead of the general ledger so i can have more detail on my drill through.
I’ve just add some 4 or 5 accounts to this particular exercise and these are all the columns that i have there. Because, i’m not an accountant, and i always do debits for assets and credits for liabilities. Could you please explain me better what did you mean?
Thanks a lot
As all of you know, to do a balance sheet, there’s some accounts (in my country 11, 12, 24, 25, 26, 27) that if they have debits goes to a sub category on assets but if they have credits, goes to a sub category on liabilities. And this is my “rule” in M code before i merge all other simple accounts:
= Table.AddColumn(Source, “SubCategory”, each
if List.Contains ( { “11”, “12”, “25” }, [Grp 02 Acc]) and [Credit] > 0 then “Financiamentos bancários-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 02 Acc] = “26” then “Accionistas Passivo-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 02 Acc] = “24” then “Estado Passivo-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 03 Acc] = “272” or Text.StartsWith([AccountID], “2782” ) then “Outros P-Total Passivo-Capital Próprio e Passivo”
else if [Debit] > 0 and Text.StartsWith([AccountID], “2781”) or Text.StartsWith([AccountID], “2782”) and [Grp 03 Acc] = “232” then “Outros AC-Total Ativo corrente-Ativo”
else if [Debit] > 0 and [Grp 03 Acc] = “281” then “Diferimentos AC-Total Ativo corrente-Ativo”
else if [Credit] > 0 and [Grp 02 Acc] = “23” then “Pessoal-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 03 Acc] = “282” then “Diferimentos P-Total Passivo-Capital Próprio e Passivo”
else if Text.StartsWith([Grp 02 Acc], “6”) or Text.StartsWith([Grp 02 Acc], “7”) then “Resultado Líquido-Total Capital Próprio-Capital Próprio e Passivo”
else null)
Thank you !!
Hi @pedroccamara,
I think you might want to have an accountant within your company to help you with this financial reporting, you will run into big issues if you don’t get this right. Issuing wrong financial statement will be get the company and you into trouble.
Assets usually have debit balances but there are some asset accounts classification that will be credits balance like accumulated depreciation.
Equity and liabilities are usually credit ending balance but can if debit balances and you need to understand how it affect those accounts.
Everything must balance Asset = Liabilities add owner Equity area
Usually the financial accounting team will have a chart of accounts that will show you proper groupings that will be able to help you with the balance sheet groups. You should be able to get an example of the balance sheet for the company and how its lay out.
I come from a financial background.
I don’t want to discourage what you are doing but i feel that you might get this wrong when issues these reports.
sorry its hard to explain.
Good Luck with the project
Keith
You’re absolutely right @Keith
But i want to learn how to do these reports. I never said to them that i am an accountant. I just know (barely) how to work with data.
And yes, because the balance is not equal i’m checking my queries. I believe the error is there, although the values are much different than those from the general ledger.
Please feel free to step up any time and teach me whatever you feel it’s gonna help me.
Thanks a lot Keith!
I tried with your M code and for all the rows i was getting null then i just used your List.Contains and found that it is not returning the correct result not sure why. But I have tried with different method. Also i could not find [Grp 02 Acc] column so i have replaced with [Grp 03 Acc] you can update it in the below code.
By using Text.Contains but you need to use multiple times and if your list if bigger then it would be difficult.
By using Splitter.SplittextByAnyDelimitter this will return the values. Please find below is the M code.
let
Source = Excel.Workbook(File.Contents(“C:\Users\welcome\Downloads\DataTransactions.xlsx”), null, true),
DataTransactions_Table = Source{[Item=“DataTransactions”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(DataTransactions_Table,{{“Grp 03 Acc”, type text}, {“AccountID”, type text}, {“B Transactions”, Int64.Type}, {“B Values”, type number}, {“Credit”, type number}, {“Debit”, type number}, {“TB Values”, Int64.Type}, {“Transactions”, type number}}),
//Update the ListOfWords as per your requirement
ListOfWords = List.Buffer( {“11”, “12”, “25”}),
Custom1 = Table.AddColumn(#“Changed Type”, “SubCategory”, each
if List.Count(Splitter.SplitTextByAnyDelimiter(ListOfWords)([Grp 03 Acc])) > 1 and [Credit] > 0 then “Financiamentos bancários-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 03 Acc] = “26” then “Accionistas Passivo-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 03 Acc] = “24” then “Estado Passivo-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 03 Acc] = “272” or Text.StartsWith([AccountID], “2782” ) then “Outros P-Total Passivo-Capital Próprio e Passivo”
else if [Debit] > 0 and Text.StartsWith([AccountID], “2781”) or Text.StartsWith([AccountID], “2782”) and [Grp 03 Acc] = “232” then “Outros AC-Total Ativo corrente-Ativo”
else if [Debit] > 0 and [Grp 03 Acc] = “281” then “Diferimentos AC-Total Ativo corrente-Ativo”
else if [Credit] > 0 and [Grp 03 Acc] = “23” then “Pessoal-Total Passivo-Capital Próprio e Passivo”
else if [Credit] > 0 and [Grp 03 Acc] = “282” then “Diferimentos P-Total Passivo-Capital Próprio e Passivo”
else if Text.StartsWith([Grp 03 Acc], “6”) or Text.StartsWith([Grp 03 Acc], “7”) then “Resultado Líquido-Total Capital Próprio-Capital Próprio e Passivo”
else null)
in
Custom1
Hope this is what you were looking for.
Thanks
Mukesh
I’m so sorry @MK3010 but you don’t need the [Grp 02 Acc].
That one is a 2 digit code. You just need [AccountID] for the 2781* and 2782* and [Grp 03 Acc] for 232.
.
You know, my main problem is this one line:
else if [Debit] > 0 and Text.StartsWith([AccountID], “2781”) or Text.StartsWith([AccountID], “2782”) or [Grp 03 Acc] = “232” then “Outros AC-Total Ativo corrente-Ativo”
If i fix this one line only i will fix the other and everything i believe it will be ok.
The purpose of this line is to have all the records from [Grp 03 Acc] = “232” and Text.StartsWith([AccountID], “2781”) or Text.StartsWith([AccountID], “2782”) as long as they are debit records. That’s it!
Thanks a lot MK!
Hello @MK3010
Found the problem!!! It would never work because this line (just before my one) were bad:
else if [Credit] > 0 and [Grp 03 Acc] = “272” or Text.StartsWith([AccountID], “2782” ) then “Outros P-Total Passivo-Capital Próprio e Passivo”
So much time you waste for me. I’m so sorry. But glad i found it thow.
Thanks a lot guys
I can’t see the value with that combination. So I changed the source data to match that and it working. Below screen shot FYR. Is this what you are looking for?
Also please note that your data for first 2 columns has a space in the starting you can trim it.
Thanks
Mukesh
Just chiming in late to this thread … could it be as small as an order-of-operations thing with the math, and adding brackets will help? Does
else if [Debit] > 0 and
( Text.StartsWith([AccountID], “2781”) or Text.StartsWith([AccountID], “2782”) or [Grp 03 Acc] = “232” )
then “Outros AC-Total Ativo corrente-Ativo”
work?
Greg
Yeah @Greg, that one would work but only (my mistake) if the previous one was also correct, which i did.
Thanks a lot for stopping by
Best regards
Guys, please stay safe and healthy
BTW user found the issue and solved it 