Conditional Fill Down (Base on a Column)

[qinh_ann]

Hello everyone,

I'm in need to conditionally fill down a column base on another column within the table. I currently have a table with the format somewhat like below:

After doing some certain queries, I came down with a table like above. However, for one "CUST #", there should only be one "Limit" value. For example, as column "CUST#" for rows 3&4 is empty, it should also hold "CUST #" of 1 and column "Limit" of all rows from 2-4 should hold value 30000, while row 8 should still only hold value 190000 for "Limit". I need a statement that can help me conditionally fill down the "Limit" column fulfilling what I described earlier.

Bare in mind that my original table have a bunch of nulls just like the example table I provided. The problem I'm having with filling down/up for this table is that while I have established that each CUST# should have one Limit value, the Limit value lies randomly within the same range for that specific CUST#, so I can't completely fill the column with just one direction of either up or down.

Would really appreciate anyone that can help. THANK YOU!!

[latimeria · Accepted solution]

Hi @qinh_ann ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDBCoAgDAbgd9nZQ1KQHZNidAvdJcT3f42cNpolMuHzd4opgQUDq4+llhkgmwRVsNRx4MFbwvEJVphYEJvEPmM/ByVWYWY5iGOLvsFVv9hdY5IGeyDFooFItbW85XGTvmU9JamsZe3vVRqoF/d+RL4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cust#" = _t, #"Cust Name" = _t, Limit = _t, Terms = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Cust#", "Cust Name", "Limit", "Terms"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Cust#"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Cust#"}, {{"Rows", each _, type table [#"Cust#"=nullable text, Cust Name=nullable text, Limit=nullable text, Terms=nullable text]}}),
    #"Fill Down" = Table.TransformColumns(
        #"Grouped Rows", 
        {
            {"Rows", each Table.FillDown(_, {"Limit"})}
        }
    ),
    #"Fill Up" = Table.TransformColumns(
        #"Fill Down", 
        {
            {"Rows", each Table.FillUp(_, {"Limit"})}
        }
    ),
    Expanded = Table.Combine(#"Fill Up"[Rows])
in
    Expanded

[qinh_ann]

@latimeria,

This works! Thank you so much!!

[nickvanmaele]

Hi @qinh_ann ,

Just to understand your data a bit better: Why do rows 1 to 4 need to have the same Cust# if their description is different? I would think that ABS, ABG, and ASS are three different customers, since they have three different descriptions, so why would all of them receive customer number 1? 

As a temporary workaround, if your table isn't too big, you can manually fill data down in Excel as described on this link

[qinh_ann]

Hi @nickvanmaele ,

The attached is just a made-up data with the same format as the data set I actually need help with, which is marked confidential 🙂 It is a part of a data cleansing project from a very messed up report (which, unfortunately, do not come in tabular format) and it's my job to cleanse that before creating interactions and such. All in all, only the first row of every row trio comes with the same Cust# is the real name of the entries (f.e. row 1), while the other two (f.e. row 2&3) only contain random value from the original report, such as address or phone number, and will be deleted accordingly later on. 

And unfortunately, it did came in with more than 20k entries for each Cust# so I can't really process it manually 🙂