Forum Discussion
How to fill down values in one column based upon distinct values in another?
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:
- Use the List.Distinct() function to find unique 'Server' values
- Select the first row per unique 'Server' value to get 'Updates' value
- 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.
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
6 Replies
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
Hi, i have similar problem may you please help me with this?
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
I am not sure what i am doing wrong but it is giving me an error
I am really new . when you mean source it is the excel source yes?
