IF FUNCTION IN POWER QUERY

[aam1932]

Hai all

Any possible that i want the result to determine D (debit) C (credit) not only based only amount column, but based on the total that particular account in the table as below:
As example, acc_code now currently showing debit amount of 19746434.24, how ever actually i have fews other BU and if i total up all the account code the amount negative, the result in the column D C, should be C and not D.

Thank you

 

[GraH]

If I understand correctly, can't you use a Group By first, then apply the If and finally expand the Grouped Table again?

GRoup By on BU, use aggregations SUM of Amount and All Rows (to keep all details available). Extract data from the tables created by All Rows you need.

 

[aam1932]

Thank you for your reply,

I can’t used group by due to, I still need report by individuals BU. The grouping of eirther D or C determine from overall total and not based on individual balance.

thank you

 

[sandy666]

maybe post representative example data and expected result.
use xl2bb or link to shared excel file

 

[aam1932]

i have problem install XL2BB, but below the sample data (only show sample one account). FYI, FY only have 2 items, either Closing Mar20 or Opening Dec19

Thank you

 

[sandy666]

try again (picture is useless - data not copyable) select the range you show, copy then paste to the post
no one want to retype your example, sorry

btw. the text about expected result should be generated also?
I asked about expected result (table) not description

 

[aam1932]

below the table, thank you

BU	FY	ACC_CODE	Sum of AMOUNT	Expected result
MGO	Closing Mar20	19657	22,707.56	D
RLL	Closing Mar20	19657	5,536,450.00	D
RLO	Closing Mar20	19657	520,860.00	D
RTL	Closing Mar20	19657	(1,148,339.07)	D
RLL	Opening Dec19	19657	(5,475,846.00)	C
RLO	Opening Dec19	19657	520,912.00	C
* No - for closing Mar 20 all line should be D
and for Opening Dec 19 all line should be C

 

[sandy666]

check if this is what you want for this source only

or post REPRESENTATIVE example of source data

 

[aam1932]

check if this is what you want for this source only
View attachment 14135
or post REPRESENTATIVE example of source data

this is an example only

 

[GraH]

Thank you for your reply,

I can’t used group by due to, I still need report by individuals BU. The grouping of eirther D or C determine from overall total and not based on individual balance.

thank you

Group by on BU Will keep all BUs. So I don't understand the feedback. Also I mention to use All Rows as an aggregation. Meaning you can extract again all details from those subtables.