cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dannyboc
Frequent Visitor

Replacing null values with the most recent per category

Hello,

 

I have the following product table. The custom column assigns with Power Query the same values for quantity, and replaces null with the most recent value for each Product code, when it's available. Otherwise, the result is null. I tried using column from examples, but there is no solution. Any ideas?

 

Thank you

 

 

Product CodeDate                     Quantity     Custom Column               
ABC1231/20/202111
BED1232/15/202122
BED2342/20/202122
CED2343/20/202111
EFG6774/1/202133
EFG6774/5/2021null3
EFG6774/7/202122
HFJ1235/1/2021nullnull
HFJ1235/4/202111
UIG8885/8/202111
UIG8885/12/2021null1
UIG8885/20/202122
1 ACCEPTED SOLUTION

@dannyboc 


after passing the code, click OK, then remove the word "each" at the beginning 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@dannyboc 

You need to add a custom column:

(t)=>  
List.Last(Table.SelectRows(#"Changed Type", each  _[Product Code] = t[Product Code] and _[Quantity] <> null and _[Date] <= t[Date])[Quantity])

Fowmy_0-1634799681849.png


The file is attached below.

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

dannyboc
Frequent Visitor

Hi @Fowmy 

 

Thank you for your reply! For some reason the result in my end is a function for each row, not a value.

 

I checked the format of each column and looks fine. Any ideas?

 

Thank you

@dannyboc 


after passing the code, click OK, then remove the word "each" at the beginning 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

dannyboc
Frequent Visitor

Thank you so much @Fowmy ! This worked for me

Hi @Fowmy ,

Running the code in Power query works, however it takes a lot of time after Close and Apply, after leaving the pc for a lot of hours, the table didn't loaded. Is there any way to calculate the column in DAX rather than Power Query?

 

Thanks

Vera_33
Super User
Super User

Hi @dannyboc 

 

I am not a fan of example column, it looks like a Fill Down, you can try this way

 

Vera_33_0-1634798614139.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc87DoAgDAbgu3QmwRYUVh/42p2INzBu3l+FgKAx6UDzpX+LtVA3LZIABsipuIrwfsPKLDSm80Qcy0AUiIR09Ex5agOJT6Dph0qpq5Ucg4i3xE37sW1vVPmusZ/9heUTGMcSlPkdyzRorZ3oX0H6JCaa/Xo9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Code" = _t, Date = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Code"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillDown([allrows],{"Quantity"})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Product Code", "Date", "Quantity"})
in
    #"Expanded Custom"

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors