Power Bi - Remove Null Rows when another value aleady exists

[Andrewdj]

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:

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.

[Ashish_Mathur · Accepted solution]

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.

[Idrissshatila]

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.

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

Follow me on Linkedin
Vote For my Idea 💡