Forum Discussion
Replace null with relative cell reference
IN that case, why not Use the FillDown function on the Invoice Date column, thus filling in the nulls with the date above. Then you can filter out the nulls in the Product/Item Description.
---Nate
Hello Anonymous
Use Table.Group and apply different function to aggregate the data you need of the grouped table. I made an example with the data you provided. For the invoice-column it takes the first value that is other than null. Add new functions for all other columns you want to aggregate.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00DMw1DMyUIrVgQjgYDgBGUamegZGMLVOyDJIjFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice Number" = _t, #"Invoice Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Number", type text}, {"Invoice Date", type date}}, "de-DE"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice Number"}, {{"Invoice Date", each List.Select( _[Invoice Date], each _ <> null){0}, type date}})
in
#"Grouped Rows"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Wow Jimmy801! I have no shame in admitting you skill is beyond my own; which is no doubt why you are the Super User! 😉
I used your cut&paste, without the faintest idea (I viewed in advanced editor as directed). As noted in your screen shot a Custom Invoice Numer Column was created and a Custom Invoice Date Column was created with the dates of 1/18/20 and 2/25/20 created for each Custom Invoice A and B, respectively.
Because I do not "understand" the code that well, I am having a difficult time manipulating it to arrive at the intended results.
I appreciate your thoughts, but am also aware that my ability combined with complexity of the task may be too large for this forum. Thank you!