Forum Discussion

JollyRoger01's avatar
JollyRoger01
Icon for Helper III rankHelper III
5 years ago
Solved

How to fill blank cells based on other cell values

I have the following table which is an output Activity Statement from PayPal. PayPal makes it ridiculously hard to reconcile with bank accounts because they enter 4 or 5 different transactions for what is actually just one, and they also don't add the supplier names to all rows.

 

Anyway, what I am trying to do here is fill in those mising supplier names. I can't use fill-down or fill-up because sometimes the suppplier names are in the middle of the blank cells. I want to fill in the blank cells in the 'Name' column with the 'Name' value of a non-blank row where the 'Date' and 'Amount' values are the same.

 

I am using Power Query in Excel for this.

 

Need Help
  • samdthompson's avatar
    samdthompson
    5 years ago

    Hello. Is there a transaction ID at all, something that would give each group of transactions something unique? If so I would sortby that then the name which would allow you to do a fill dwn or up. If the volume of transactions is quite low and the likelyhood of an exact match on transaction time very low then you could sortby Date, Time, Name then fill down/up.

     

4 Replies

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    5 years ago

    Hello. Is there a transaction ID at all, something that would give each group of transactions something unique? If so I would sortby that then the name which would allow you to do a fill dwn or up. If the volume of transactions is quite low and the likelyhood of an exact match on transaction time very low then you could sortby Date, Time, Name then fill down/up.

     

  • KNP's avatar
    KNP
    Icon for Super User rankSuper User
    5 years ago

    Hi JollyRoger01, 

     

    Assuming the lack of some type of ID column, in PQ, you could reference the original table and group the data, getting a MAX of the name column (replace nulls first if necessary) and then merge the orignal and grouped as a new query joined on Date, Time and the absolute value of amount and then expand name. Delete columns as required.

     

    Example...

    Setup three blank queries named Original, Grouped and Final and paste the relevant code below into each to see what I mean.

     

    Original:

    // Original
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hYxBCoAwDAT/knNtk21E7E30F6X//4aLeKhgFTIsZIetVSypJShMgpgWVeZGJqi08N7z7hZ9i4LM3Il7dB8av8JzIvfGXPi9JqYlR6xD4yDfRrfRTg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Name = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Name", type text}, {"Amount", type number}}),
    #"Inserted Absolute Value" = Table.AddColumn(#"Changed Type", "Absolute Value", each Number.Abs([Amount]), type number)
in
    #"Inserted Absolute Value"

     

     Grouped:

    // Grouped
let
    Source = Original,
    #"Replaced Value" = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,{"Name"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Date", "Time", "Absolute Value"}, {{"Name", each List.Max([Name]), type nullable text}, {"Amount", each List.Sum([Amount]), type nullable number}})
in
    #"Grouped Rows"

     

    Final:

    // Final
let
    Source = Table.NestedJoin(Original, {"Date", "Time", "Absolute Value"}, Grouped, {"Date", "Time", "Absolute Value"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Name"}, {"Table1 (2).Name"})
in
    #"Expanded Table1 (2)"

     

    Hope that helps.

     

    Kim

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Or like this with FillUp/Down...

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZPLbsIwEEV/ZZQ1BNt5Ce9CQGxARW1RF4iFS0yxauLIMW35+5o+CJRAE5Ci2IrjM3fu6M5mDgk6gPwOEESw03IQRRHFyO7iwcOjXabPXEO8KQxoJgUwmJgtjExqjyaaQ5vluVZvPIUJ2655ZqAnpIRpYW/9fLF/JmqdS2747lY87dt320eu13XmrZsUDHnGNQNpj81KaQEFMwJU1rAkoTigOCpL7tESEs1TYSBhOoU+z1UhqhvC5BIaSvZYLLQq1NLs+nBHYi2+MdbNq83cF+8eFQ9pEFLfu6mvCNUi8x7b2mXwkWteFJCs+OJVbcxl2c3Yv8przboaHVHkUT+sMATqO+KHbhTUol9lSmN8wxCUeEsP9nSM6e45wA+VepHAq6Ln3pz+0CVfIjA6UtGlxKPIPxBBYje5G5cxaWgmcaOoVqErolEfftJFw5mVhf4MDVG/S/FpVn7Hcc+XmwzSS8n+F3kuI0/CrFLN3pk8H8H5Jw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, #"Time zone" = _t, Name = _t, Type = _t, Status = _t, Currency = _t, Amount = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Name"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Date", type date}}, "hr-HR"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Time", "Time zone"}, {{"Gr", each Table.FillUp(Table.FillDown(_,{"Name"}),{"Name"}), type table}}),
    #"FINAL" = Table.ExpandTableColumn(#"Grouped Rows", "Gr", {"Name", "Type", "Status", "Currency", "Amount"})
in
    #"FINAL"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    If you make a new column using Number.Abs to make the amount column positive, you can then group on the Amount column, and add the GroupKind.Local for the optional parameter. This will group each new value in the column, even if there are duplicates. Then you can just pull the name columns and then that's it! Give it a try. I'm in my phone or I would post the code. 
    --Nate.