Remove the cell contents if associated cell in another column has exact value

Power Query
1

Hi,
Sorry that everything is in Arabic, this is why I’ve posted an image.

image
image1921×1025 155 KB

Please, I’ve two identical columns, one is Amount for $ and the second is Percentage for %.
If the Analysis Type column = Balance, then I want to keep the contents under the Amount column.
If the Analysis Type column = Vertical OR Horizontal, then I want to keep the contents under the Percentage column.
So how to do it?
If I attached the file while it is in Arabic, is it ok, or not useful?

Thanks

3

Thanks for your fast reply.
I do not mean the other columns, but I mean the columns included within the red shapes.
These columns are in English. The other Arabic ones do not matter to me.
I hope that I was able to successfully express my idea.
Thanks

4

Hi @MGadAllah

If you go Add Column → Conditional Column on the top ribbon:

image

You can add a column to calculate this.

image
image940×402 9.6 KB

If the options in Analysis Type are always Balance, Verticle or Horizontal, then you can just do the Balance one in the If and have the Percentage in the Else. If there are other possible options, you can click the Add Clause button to add a line for each of the 3 scenarios. Attached is an example.

MGB Custom Column.pbix (17.9 KB)

Thanks,

1 Like
5

Hi Mohamed,

Arabic or not for the other columns doesn’t matter, your description seems very clear.

What you described in the first post you can achieve with these two steps:

    #"Replaced Balance" = Table.ReplaceValue(#"previous Step",each [Percentage],each if [Analysis Type]="Balance" then null else [Percentage],Replacer.ReplaceValue,{"Percentage"}),
    #"Replaced Vertical" = Table.ReplaceValue(#"Replaced Balance",each [Amount],each if [Analysis Type]="Vertical" then null else [Amount],Replacer.ReplaceValue,{"Amount"})

It is the initial column by column approach described here starting around the 4th minute:

Regards,
Matthias

2 Likes
6

Thanks a lot for your replies.
Both solutions worked for me.
Much appreciated your valuable time helping me.

7

@Keith
I’ve realized that you also removed your posts from this thread and other threads.
I hope that I did not say or do anything annoyed you.
If anything annoyed you, please accept my sincere apologies.
Thanks,
MGB