topic Re: How to generatively fill 'null' cells in Desktop

How to generatively fill 'null' cells

Anonymous — Wed, 06 Mar 2024 09:47:05 GMT

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!

Re: How to generatively fill 'null' cells

_AAndrade — Wed, 06 Mar 2024 09:57:06 GMT

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.

Re: How to generatively fill 'null' cells

Anonymous — Wed, 06 Mar 2024 09:59:22 GMT

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!

Re: How to generatively fill 'null' cells

PowerBigginer — Wed, 06 Mar 2024 10:00:51 GMT

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

Re: How to generatively fill 'null' cells

Daniel29195 — Wed, 06 Mar 2024 10:11:12 GMT

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

Re: How to generatively fill 'null' cells

_AAndrade — Wed, 06 Mar 2024 10:11:49 GMT

And use the Replace Values option like this?:

Re: How to generatively fill 'null' cells

Anonymous — Wed, 06 Mar 2024 10:16:47 GMT

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 🙂

Re: How to generatively fill 'null' cells

Anonymous — Wed, 06 Mar 2024 10:17:40 GMT

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