Need help on excel conditional formula

[KuntalSingh]

Hi 

I have need to apply excel formula 

IF(ISBLANK([@Lookup])=FALSE,"MIS-2",IF(AND([@MIGO]<0,ISBLANK([@MIRO])=TRUE),"MIS-5",IF(AND([@MIGO]>0,ISBLANK([@MIRO])=TRUE),"MIS-1",IF(AND([@MIRO]<>0,ISBLANK([@MIGO])=TRUE),"MIS-4",IF(AND(ISBLANK([@MIRO])=FALSE,ISBLANK([@MIGO])=FALSE),"MIS-3","")))))

in power query 

Conditions are 

1. MIGO Column has a negative value and MIRO column is empty – MIS 5
2. MIGO column has positive value and MIRO is empty – MIS 1
3. MIGO Column is empty and MIRO column has a value – MIS 4
4. MIGO MIRO Column both have a value – MIS 3

Please find below the sample data 

RefKey3 Purchasing Document MIGO MIRO Grand Total Lookup Status
40837604 27659853 14040.34 14040.34 MIS-1
40856544 27759530 7571.2 7571.2 MIS-1
40909663 27550294 -219525.06 -219525.06 MIS-5
41059635 27956892 -982583 -982583 MIS-5
41062015 27959015 -1909281 -1909281 MIS-5
41063446 27958936 17553.28 17553.28 MIS-1
41097136 27991728 -1396371 -1396371 MIS-5
41154529 27983371 -65132.18 -65132.18 MIS-5
41154547 27983371 -74934.91 -74934.91 MIS-5
41204070 27595735 -149958.99 -149958.99 MIS-5
41244021 28092499 5522.4 5522.4 MIS-4
41354750 27599843 -6198.75 -6198.75 MIS-4
41382471 28252523 36502.47 36502.47 MIS-4
41430718 28303756 -2377.64 -2377.64 MIS-5
41488782 28308304 19000 19000 MIS-4
41508140 28376909 25586.21 25586.21 MIS-4
41516145 27867971 -19507.07 -19507.07 MIS-4
41524563 27930802 -37509.76 -37509.76 MIS-5
41534497 27930964 -10319.32 -10319.32 MIS-5
41535174 27930964 -85511.17 -85511.17 MIS-5
41535195 27930964 -42905.91 -42905.91 MIS-5
41535226 28316334 20900 20900 MIS-4
41535253 27585136 -8063.74 -8063.74 MIS-5
41535261 27689899 -8250.25 -8250.25 MIS-5
41535310 27744865 -51516.67 -51516.67 MIS-5
41535318 27920323 -8292.51 -8292.51 MIS-5
41540032 28409974 47000 47000 MIS-1
41540620 27949121 -40420.52 -40420.52 MIS-5
41540719 27949116 -42191.95 -42191.95 MIS-5
41540742 28104400 -10129.87 -10129.87 MIS-5
41546215 28301118 18000 18000 MIS-4
41560626 27805410 -17179.94 -17179.94 MIS-5
41560645 27805410 -31937.1 -31937.1 MIS-5
41560946 27842300 -102403.05 -102403.05 MIS-5
41561075 27852885 -33793.93 -33793.93 MIS-5
41561306 28047256 -6252.96 -6252.96 MIS-5
41561340 28047256 -26809.6 -26809.6 MIS-5
41561423 28047223 -9770.37 -9770.37 MIS-5
41576775 28121627 1 1 MIS-1
41585078 27599652 -6198.75 -6198.75 MIS-5
41585236 27599652 -6198.75 -6198.75 MIS-5
41585595 27599652 -6198.75 -6198.75 MIS-5
41585641 27599652 -6198.75 -6198.75 MIS-5
41585855 27575969 -21792.87 -21792.87 MIS-5
41588240 28258635 -7395.59 -7395.59 MIS-4
41592176 26226846 -604741.47 604668 -73.47 MIS-3
41599309 27689885 -11218.05 -11218.05 MIS-5
41599644 27689885 -10865.56 -10865.56 MIS-5
41604734 27657819 -106027.21 -106027.21 MIS-5
41604868 27782876 -56285.78 -56285.78 MIS-5
41627597 28251247 -138603.82 -138603.82 MIS-5
41627602 28251247 -91562.44 -91562.44 MIS-5
41628381 28237850 -12401.46 -12401.46 MIS-4
41636138 27570149 -30278.99 -30278.99 MIS-5
41636341 27693789 -46550.86 -46550.86 MIS-5
41636344 27867972 -30694.85 -30694.85 MIS-5
41649276 28214123 -67858.83 -67858.83 MIS-5
41649279 28214123 -20918.71 -20918.71 MIS-5
41649285 28214123 -48362.23 -48362.23 MIS-5
41650978 28214123 -11158.05 -11158.05 MIS-5
41650989 28214123 -11119.46 -11119.46 MIS-5
41650998 28214123 -11077.94 -11077.94 MIS-5
41651001 28214123 -11045.53 -11045.53 MIS-5
41651008 28214123 -10953.17 -10953.17 MIS-5
41651013 28214123 -11041.41 -11041.41 MIS-5
41662535 28516666 -5106.66 -5106.66 MIS-5
41662539 28516815 -10459.42 -10459.42 MIS-5
41669260 27575969 -67836.47 -67836.47 MIS-5
41669264 27575969 -9466.76 -9466.76 MIS-5
41676214 28230811 -8154.79 -8154.79 MIS-5
41677698 27816038 -190049.38 -190049.38 MIS-4
41684539 28466041 -39061.9 -39061.9 MIS-5
41701813 28555354 -16650 -16650 MIS-5
41719121 22208123 -13198.34 -13198.34 MIS-5
41719124 22208125 -37762.77 -37762.77 MIS-5
41733595 28586817 -19047 -19047 MIS-5
41737146 28108813 -75642.39 -75642.39 MIS-5
41737187 28108813 -78654.5 -78654.5 MIS-5
41737254 28423935 -21935.28 -21935.28 MIS-5
41764728 28611939 -470102 -470102 MIS-5
41764746 28611939 -45000 -45000 MIS-5
41776561 28559242 -3058.72 -3058.72 MIS-5
41776684 28559242 -3058.72 -3058.72 MIS-5
41790462 28632525 -19909.61 -19909.61 MIS-5
41791189 27626219 -2162.83 2162.83 0 MIS-3
41792067 27626217 -948 -948 MIS-5
41792087 27626217 -1067.12 -1067.12 MIS-5
41792110 27626217 -2111.53 -2111.53 MIS-5
41792248 27626223 -474.35 -474.35 MIS-5
5946551517 28702956 -354 88.5 -265.5 5946551517 MIS-2
5947103354 28794199 -850 -850 5947103354 MIS-2
5947272183 28963481 -236 -236 5947272183 MIS-2
5947318192 28794199 -850 -850 5947318192 MIS-2
5947341120 28883398 41480 41480 5947341120 MIS-2
5947391275 28883398 48800 48800 5947391275 MIS-2
5947443540 28883336 -2168061 2180981 12920 5947443540 MIS-2
5947528727 28883364 -4125 -4125 5947528727 MIS-2
5947567413 28883594 -27434 -27434 5947567413 MIS-2
5947582764 28883336 -11020 -11020 5947582764 MIS-2
5947640729 28883544 -41480 -41480 5947640729 MIS-2
5947657791 28969514 -16875 -16875 5947657791 MIS-2
5947669692 28883544 -35380 -35380 5947669692 MIS-2

[mlsx4 · Accepted solution]

Hi @KuntalSingh 

Data is not well inserted so I can't reproduce your case. I'm not sure when you need MIS-2. But, with the data I have understood, you can do:

= Table.AddColumn(#"Tipo cambiado", "Personalizado", each if [MIGO]<0 and [MIRO]=null then "MIS-5" else 
  if [MIGO]>0 and [MIRO]=null then "MIS-1" else
  if [MIGO]=null and [MIRO]<>null then "MIS-4" else
  if [MIGO]<>null and [MIRO]<>null then "MIS-3" else "MIS-2")

[KuntalSingh]

Thanks for your reply

I already used the same formula.

= Table.AddColumn(#"Filtered Rows", "MIS", each if[Lookup]<>null then "MIS2" else if [MIGO]<0 and [MIRO]=null then "MIS5" else if [MIGO]>0 and [MIRO]=null then "MIS1" else if [MIRO]=null and [MIGO]<>null then "MIS4" else if [MIRO]<> null and [MIGO]<>null then "MIS3" else "")