Power Bi - Fill in Null values if data exists in other rows

Question

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.

Did any of these answers work for you? If so, please toggle checkmark to accept that solution for your question — horseyride
– horseyride, Commented Aug 28, 2023 at 12:35

davidebacci · Accepted Answer · 2023-08-18 22:02:02Z

1

Please don't paste pictures of data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Company", type text}, {"Sales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Company Fixed", (x)=> let
        a = Table.SelectRows(#"Changed Type", each _[Name] = x[Name]),
        b = try List.Distinct(List.RemoveNulls(a[Company])){0} otherwise null
        in b)
in
    #"Added Custom"

Add a custom column and type in the following:

(x)=> let
a = Table.SelectRows(#"Changed Type", each _[Name] = x[Name]),
b = try List.Distinct(List.RemoveNulls(a[Company])){0} otherwise null
in b

answered Aug 18, 2023 at 22:02

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

horseyride · Accepted Answer · 2023-08-18 23:58:52Z

0

You could also merge the data on top of itself

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"Name"}, Table.Distinct(Table.SelectRows(Source, each [Company] <> null), {"Company", "Name"}), {"Name"}, "search", JoinKind.LeftOuter),
#"Expanded search" = Table.ExpandTableColumn(#"Merged Queries", "search", {"Company"}, {"Newcompany"})
in  #"Expanded search"

answered Aug 18, 2023 at 23:58

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Collectives™ on Stack Overflow

Power Bi - Fill in Null values if data exists in other rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related