Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Code | Date | Quantity | Custom Column |
ABC123 | 1/20/2021 | 1 | 1 |
BED123 | 2/15/2021 | 2 | 2 |
BED234 | 2/20/2021 | 2 | 2 |
CED234 | 3/20/2021 | 1 | 1 |
EFG677 | 4/1/2021 | 3 | 3 |
EFG677 | 4/5/2021 | null | 3 |
EFG677 | 4/7/2021 | 2 | 2 |
HFJ123 | 5/1/2021 | null | null |
HFJ123 | 5/4/2021 | 1 | 1 |
UIG888 | 5/8/2021 | 1 | 1 |
UIG888 | 5/12/2021 | null | 1 |
UIG888 | 5/20/2021 | 2 | 2 |
Solved! Go to Solution.
after passing the code, click OK, then remove the word "each" at the beginning
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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])
The file is attached below.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
after passing the code, click OK, then remove the word "each" at the beginning
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Hi @dannyboc
I am not a fan of example column, it looks like a Fill Down, you can try this way
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |