Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdrammeh
Helper III
Helper III

How to use DAX to return values for blanks in a given column in Power Query

I have two product codes grouped in two columns. Each Product Code came from a separate query but only one of them contains the true value. In column “C,” I want my “Result” column to return the following values:

 

Results =If Product Code 1 value is blank or not equal to Product Code 2, return the value in Product Code 2, Otherwise Return the value in Product Code 1

 

Product Code 1Product Code 2Results
39508073950807 
39822423982242 
 3982252 
40763274076327 
40800104080010 
40800314080031 
39714053971405 
39715103971510 
40082254008225 
40812844081284 
40812864081286 
40813224081322 
39774773977477 
39717273971727 
 3983696 
39714103971410 
40812394081239 
 3976141 
40704364070436 
1 ACCEPTED SOLUTION

Hi @mdrammeh,

 

Based on my test, you should be able to use the following power query(M) to add a new custom column under Query Editor > Add Column tab. Smiley Happy

Result = if[Product Code 1] = null or [Product Code 1] <> [Product Code 2] then [Product Code 2] else [Product Code 1]

mc1.PNG

 

Regards

View solution in original post

5 REPLIES 5
Sean
Community Champion
Community Champion

How about...

Results =
IF (
    ISBLANK ( 'Table1'[Product Code 1] )
        || 'Table1'[Product Code 1] <> 'Table1'[Product Code 2],
    'Table1'[Product Code 2],
    'Table1'[Product Code 1]
)

Hi #Sean,

 

Thanks for getting back to me. Is this a Power Query formula or Power BI formula? The reason I ask is am using Power Query for this solutions.

 

Thanks again!

Hi @mdrammeh,

 

Based on my test, you should be able to use the following power query(M) to add a new custom column under Query Editor > Add Column tab. Smiley Happy

Result = if[Product Code 1] = null or [Product Code 1] <> [Product Code 2] then [Product Code 2] else [Product Code 1]

mc1.PNG

 

Regards

Anonymous
Not applicable

The optimiser in me thinks i should suggest:

 

Results =
IF (
    'Table1'[Product Code 1] = 'Table1'[Product Code 2],
    'Table1'[Product Code 1],
    'Table1'[Product Code 2]
)

 

The reason i dropped the "BLANK" requirement is there isn't a scenario where you can get blank incorrectly.  Because if Product Code 1 is blank, then it cannot equal product code 2 unless it is also blank.  So unless i'm missing something, we only need to check if both are equal.

Thanks for the help #Ross73312 but what am trying to do here is to return a value for the blank rows in "Product Code 1". The reason is Product Code 1 has incomplete values that are present in Product Code 2. 

 

The second question is, would this formula work with Power Query? It looks more like a PowerBI formula. Please advice. 

 

Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.