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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors