How to generatively fill 'null' cells

[Anonymous]

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!

[PowerBigginer · Accepted solution]

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 advance

[_AAndrade]

And use the Replace Values option like this?:

[Daniel29195 · Accepted solution]

@Anonymous 

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! 🤠:

[Anonymous]

Wow this is really clever! Thanks so much for your help, I appreciate it 🙂

[PowerBigginer · Accepted solution]

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 advance

[Anonymous]

Hi,

Thank you, this worked perfectly! Really appreciate the help, I would never have been able to create this formula myself! Really appreciate the help 🙂 

[_AAndrade]

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.

[Anonymous]

Hi,

Yes I did think that might have been an option, however the columns contain multiple different Customer Names and Account IDs so when I've tried this it's overwritten all of the other ones above/below it so I don't think this will work. Thank you for the idea, though!