Latest Enterprise DNA Initiatives

Balance sheet query

Hello all
I’m trying to do a balance sheet and this is what i have. Balance.xlsx (10.8 KB)
Now, my idea is to check (because i have to) every account ID and the Liquid value if is negative or positive and give it a name, for example, like this:

else if [AccountID] = “22” and [Liquid] < 0 then “Fornecedores=Passivo corrente=Capital próprio e passivo”
else if List.Contains ( { “264”, “265”, “268” }, [AccountID]) and [Liquid] < 0 then “Accionistas (sócios)=Passivo corrente=Capital próprio e passivo”
else if [AccountID] = “24” and [Liquid] < 0 then “Estado e outros entes públicos=Passivo corrente=Capital próprio e passivo”

and so on. But you can imagine how many “else ifs” i have…
Is there any other (faster) way of doing it? I thought on merge a table but the problem is that i also have to check the liquid value.
Can you help me?
Thanks a lot
Pedro

Hi @pedroccamara,

If you have a table with associated names in another query a “merge” sounds much more friendly… However non of that was present in your sample so I made something up.

Created a tName query that contains a Name for each AccountID
Next added a Custom Column, first testing the value (green) and when it passes retrieving the name (yellow) otherwise returning a null - you can adjust this to your actual needs.

Here’s your sample file. Balance.xlsx (174.3 KB)
I hope this is helpful.

Hey @Melissa
I’m so sorry but i don’t think i told you the whole story. I have to check for each and every account id, their number AND the liquid value. Which means, for example, for account ID 232 i may have two different text names, depending on the liquid value > 0 or not.
But you gave me a brilliant idea. I will come up with an extra table, with account id and text value if liquid >0 and another text value if liquid <0. After this merge, i will choose the column that i need with something like, If liquid >0 then column 1 else column 2.
I believe this would work, right? Well, anything’s better than 65 else ifs…
Do you mind to let me know what do you think or if you’d do it another way?

Hi @pedroccamara,

Two columns would be a great work around. The support table/query.

image
.

Instead of returning a null if the condition isn’t met we can return the other column instead. Like so.

Here’s your sample. Balance.xlsx (186.6 KB)
I hope this is helpful.

2 Likes

Awesome @Melissa. Very good.
Thank you so much. Great help!