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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Andrewdj
Frequent Visitor

Power Bi - Remove Null Rows when another value aleady exists

I'm working in Power BI and I have the below table of how it is and how I want it to look, with the highlighted green cells what I'm looking to change:

 

image2.png

 

I want to fill in the null values with the company name if that data exists on another row. Take John for example, I want to fill in the null value in row 3 because I have another row with John and company is filled out. But for Tim, Dave and Andy they only have one record so I need to keep that record as null.

I'm trying to do this in Transform Data, but not sure if that's the best place to accomplish this.

Any suggestions on how to handle this would be greatly appreciated.

 
 
 
 
 
 
 
 
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

this M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each _, type table [Name=nullable text, Company=nullable text, Sales=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"All", each Table.FillUp(_,{"Company"})}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"Company", "Sales"}, {"Company", "Sales"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"Name", type text}, {"Company", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type"

Hope this helps.

Ashish_Mathur_0-1692402273607.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

this M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each _, type table [Name=nullable text, Company=nullable text, Sales=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"All", each Table.FillUp(_,{"Company"})}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"Company", "Sales"}, {"Company", "Sales"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"Name", type text}, {"Company", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type"

Hope this helps.

Ashish_Mathur_0-1692402273607.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Idrissshatila
Super User
Super User

Hello @Andrewdj ,

 

You could click on the drop down on the company column and un select null from the list and you'll reach to your desired outcome.

Idrissshatila_0-1692383895310.png

 

Idrissshatila_1-1692383915816.png

 

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea 💡



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.