How to fill down values in one column based upon distinct values in another?

[goldgate]

Hello.

I have a dataset which has gaps in a certain column ('Updates') I would like to fill down according to the distinct values in another column ('Server'):

The values in 'Update' are always in the first unique 'Server' row. However, as not every unique value in the 'Server' column has a number in 'Updates', if I were to fill down in the usual way, 'Updates' values would fill down and spill over into rows corresponding to unrelated 'Server' values, which I do not want.

Therefore, I want to develop a custom function capable of filling down values in the 'Updates' column via the Table.FillDown() function for each respective unique value in the 'Server' column.

However, I am very new to for-loops in Power Query, and I would appreciate some guidance on how to build one for this function.

I presume it would be structured like this:

1. Use the List.Distinct() function to find unique 'Server' values
2. Select the first row per unique 'Server' value to get 'Updates' value
3. Use the Table.FillDown() 'Updates' value per unique 'Server' value to achieve the fill down.

Any assistance with this would be very much appreciated. Many thanks for reading. 

[slorin]

Hi @macolmedo10 

replace "Server" by "Location" and "Updates" by "Bayer Name"

= Table.Combine(
   Table.Group(
      Your_Source,
      {"Location"},
      {{"Data", each Table.FillDown(_,{"Bayer Name"})}}
   )[Data]
)

Stéphane

[macolmedo10]

I am not sure what i am doing wrong but it is giving me an error

[macolmedo10]

I am really new .  when you mean source it is the excel source yes?

[macolmedo10]

Hi, i have similar problem may you please help me with this?

[slorin · Accepted solution]

Hi

= Table.Combine(
   Table.Group(
      Your_Source,
      {"Server"},
      {{"Data", each Table.FillDown(_,{"Updates"}), type table [Server=number, Events=number, Updates=nullable number]}}
   )[Data]
)

Stéphane 

[goldgate]

Thank you so much! This worked perfectly.