cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alleckii
Frequent Visitor

Replace exact one null in column.

Hi,

 

Edit:

I described my problem incorrectly. The data source is MS SQL, after downloading the data to PowerBI, I want to do data transformation in Power Query. One of the columns has missing data (nulls), which I would like to fill with different values. I can't do it in MS SQL because I don't have proper access to DML operations. The problem is that I can't change exactly one cell with a null value. Option replace the value replaces all nulls in the entire column.

 

I'm facing problem with replacing exact one null in column. Everywhere are tips to use replace with option, but this changes all nulls. After reading many solutions, I started wondering is it even possible to change exact one null. 

 

 

Thanks for help!
Alleckii

2 ACCEPTED SOLUTIONS

@alleckii you can't replace it but you can add a new column in PQ or DAX with a logic that will create the column you need. The logic will require that the row will have something that will distinguish it from the other rows in your table

View solution in original post

claymcooper
Resolver II
Resolver II

Are the values in the other columns unique? Meaning could you create a conditional column (either in Power Query or data view) to add in your value? Something like this: = IF( [unique column] = ABC), 25000$, [Price]

View solution in original post

9 REPLIES 9
claymcooper
Resolver II
Resolver II

Are the values in the other columns unique? Meaning could you create a conditional column (either in Power Query or data view) to add in your value? Something like this: = IF( [unique column] = ABC), 25000$, [Price]

Yes, there are unique values for every row. Conditional column will work for sure. Thanks!

pranit828
Community Champion
Community Champion

Hi @alleckii 

You can try the below code, replace the table and column name with the exact name from your report.

IF(ISBLANK('Table'[Column]),"Replacement Field", 'Table'[Column])




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

It's not what I'm looking for, but that's because I described my problem incorrectly at the begining.

SpartaBI
Community Champion
Community Champion

@alleckii maybe based on logic that will take account other columns if they differ in a way you can control which null will be replaced.
Can you share an example data and what you want to achieve

I described my problem incorrectly. From the beginning. The data source is MS SQL, after downloading the data to PowerBI, I want to do data transformation in Power Query. One of the columns has missing data (nulls), which I would like to fill with different values. I can't do it in MS SQL because I don't have proper access to DML operations. The problem is that I can't change exactly one cell with a null value. Option replace the value replaces all nulls in the entire column.

 

This is example of a column that I have in my table:

 Price 
 15000$ 
 null 
 10000$ 

 

null

 

 null 

 

null

 

 

45000$

 

 

And this is what I want to achvieve:

 Price 
 15000$ 
 null 
 10000$ 

 

null

 

 25000$ 

 

null

 

 

45000$

 

 

@alleckii you can't replace it but you can add a new column in PQ or DAX with a logic that will create the column you need. The logic will require that the row will have something that will distinguish it from the other rows in your table

Just as I thought. Great! Thanks for help!

@alleckii my pleasure 🙂

Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.