Forum Discussion
How to generatively fill 'null' cells
Hi guys,
Apologies for the confusing title, I'm not quite sure how to best word this issue!
I have a table that has two main columns: Account ID and Customer Name. The Customer Name column contains a lot of blanks but the Account ID field is complete, so I was wondering if there's a way to get PowerBI to recognise that Account ID 21 is used against the Customer Name, and therefore to fill in any blanks as a result of this? I know this is really confusing, here's an example:
So is there a way to get PowerBI to recognise that Equip = Account ID 21, therefore any rows that have the Account ID 21 should fill any blank cells in the Customer Name column with Equip?
I want these null cells filled so I can use the Customer Name as a Slicer.
I hope I've been able to explain this well enough, thank you for all help in advance!
Filled_Name = IF( ISBLANK('YourTableName'[Name]), CALCULATE( FIRSTNONBLANK('YourTableName'[Name], 1), FILTER( 'YourTableName', 'YourTableName'[Account_ID] = EARLIER('YourTableName'[Account_ID]) && NOT(ISBLANK('YourTableName'[Name])) ) ), 'YourTableName'[Name] )
Try this column If it helps Mark as a solution!
For more Power BI tips and trick you can visit https://powertipstricks.blogspot.com/ blog
Thanks in advanceAnonymous
output
sample data used :
steps
step1 : create a config table in power query , as follow :
1.1 duplicate your table.
1.2 remove duplicates
1.3 filter column1 on not blank
step2 :
merge this table with your table on id = id and expand the column : account_name ( in my scenario i named it Column1modified .
step3 : use the replace value feature under transform tab : ( be sure yo select your orignial account_name column )
enter any data to replace with .
in the code , modifiy the replace 1 with each [ column1modified]:
let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠:
7 Replies
Filled_Name = IF( ISBLANK('YourTableName'[Name]), CALCULATE( FIRSTNONBLANK('YourTableName'[Name], 1), FILTER( 'YourTableName', 'YourTableName'[Account_ID] = EARLIER('YourTableName'[Account_ID]) && NOT(ISBLANK('YourTableName'[Name])) ) ), 'YourTableName'[Name] )
Try this column If it helps Mark as a solution!
For more Power BI tips and trick you can visit https://powertipstricks.blogspot.com/ blog
Thanks in advanceAnonymous
output
sample data used :
steps
step1 : create a config table in power query , as follow :
1.1 duplicate your table.
1.2 remove duplicates
1.3 filter column1 on not blank
step2 :
merge this table with your table on id = id and expand the column : account_name ( in my scenario i named it Column1modified .
step3 : use the replace value feature under transform tab : ( be sure yo select your orignial account_name column )
enter any data to replace with .
in the code , modifiy the replace 1 with each [ column1modified]:
let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠:And use the Replace Values option like this?:
Hi,
I don't know if I get your idea, but if you want to replace null values with the same name as the previous row you can use the option Fill-->down.
