Forum Discussion

Andrewdj's avatar
Andrewdj
Frequent Visitor
3 years ago
Solved

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:

 

 

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.

 
 
 
 
 
 
 
 
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

     

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.