Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional Fill Down (Base on a Column)

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 c...
Need Help
  • latimeria's avatar
    latimeria
    3 years ago

    Hi Anonymous ,

     

    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

     

     

     

nickvanmaele's avatar
nickvanmaele
Icon for Advocate II rankAdvocate II
3 years ago

Hi Anonymous ,

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ðŸ™‚