It seems to me the current Fill function is to fill in the missing values in adjacent cells but the columns need to be consistent. If they are not, the function will fill in rubbish.
Here is what I need and I don’t see how Fill can manage it. Is there any other way doing it? I have a quite a number of columns that are inconsistent like this.
I wish in the Fill function I could set something like a control column and would decide where the fill in starts and where it ends for all columns in the table.
Here is what I need… I only can fill in the green missing fields. The others should stay empty because I do not have the required information. Fill function will instead behave the way it shows on the left. Unfortunately that is not going to work.
The Fill transform is not contextual and is not intended for this sort of transformation. However you can do it using other transforms such as Unique and Lookup:
Thank you. A bit of learning needed on my side to understand how the Lookup function works, I am a bit confused from the documentation, unless I repeat step by step. Perhaps the help page could list more examples of how the Lookup function is used. However, I think I can manage it by replicating what you do.
By looking at the help page for Unique function I see it recommends Split Col function to separate the concatenated values back to rows. I think this is going to work as well with less steps.
Was there any reason this option wouldn’t work or was there a reason I suggested the lookup and not the Split Col function?
Perhaps I didn’t explain properly in my original post when I said on line 7 … " I only can fill in the green missing fields.. What I meant is for the column Phone I need only the green missing fields. I still need to fill in the column City (I should have made that also green color to make it clear.
What I meant I need the whole table to look like the table on the right and I didn’t want to fill the phone numbers in the column Phone all the way down only where show in the green text.
So hopefully this clarifies and my guess is this could have been the reason you didn’t suggest the function Split Col?
The transform that @Admin made, only thing missing in that was the Fill transform, that you can add and choose City and it will give you the desired result.
In your Split Col solution, you need to add Replace transform to remove the comma at the end of the City name, that gets added due to Unique transform as Concat delimiter
The criteria is Name. Here Peter has 3 orders. In black text color is what comes in the CSV included. I only want Peter’s City and Peter’s Phone number to get filled in the missing fields for Peter’s orders.
Lola has no phone number, so all Lola’s Phone fields should remain empty.
I simply added your solution to the @Admin solution, that does not mean it has to be like that, I did not want to add another clipboard and just added your Split Cols transform after the Unique transform, to show that both path comes to the same result with added transform in both path, for @Admin it was Fill and for yours it was Replace.
