fill empty cells

Question

Hi!
Starting from table 1, I need some way to get the result that I indicate in table output so that the empty cells are completed in the indicated way.
My real table have +20000 rows so so i really appreciate any help.

start data (table 1)

Result after process

Thanks in advance

Regards

Answer 1

Hi @jm saf

With Get & Transform aka Power Query. With your data in Table1, use the following query code:

let  
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  
    #"Changed Type" = Table.TransformColumnTypes(Source,  
        {  
            {"Date", type date}, {"User", type number},  
            {"Pto", type text}, {"Center", type number}  
        }  
    ),  
    #"Grouped Rows" = Table.Group(#"Changed Type", {"User"},  
        {"GRP", each  
            Table.FillDown(  
                Table.FillUp(_, {"Pto","Center"}),  
                {"Pto","Center"}  
            ),  
            type table  
        }  
    ),  
    #"Combined Tables" = Table.Combine(#"Grouped Rows"[GRP])  
in  
    #"Combined Tables"  

Corresponding sample available here:

• Put your data in the blue table
• Right-click in the green table > Refresh

Answer 2

Let's say that the table is in columns A to D.
Select columns C and D.
Press F5 or Ctrl+G to activate the Go To dialog.
Click Special...
Select Blanks, then click OK.
Look at the name box on the left hand side of the formula bar. You will see the address of the active cell.
I'll assume that it is C1.
Enter the following formula and confirm it with Ctrl+Enter:

=C2

Answer 3

@jm saf

I did the test based on the answer of HansV-7609 and reproduced your issue.

If I complete the information like below screenshot, there will be no problem.


So please make sure the information of the row that is above the row you start to change is complete before using the method given by HansV-7609.

Besides, please check if the answer given by Lz-3068 is helpful to you.

Any updates, please let us know.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi!

I don't know if I'm doing something wrong, but i have done the test based on the answer of HansV-7609 in my real data, there are cases in which a user with blank records before the data of another user, puts the data of the next one in the blank records of the previous.

Lz-3068 seems to be a good solution, but I'm not familiar with power pivot. I would need to see manuals on how it works

Thanks for your interest.

Answer 5

Hi jmsaf-4508

Easiest way to understand/replicate:
1/ Take Table1 as below where there's only 1 User ID

2/ Load it table to Power Query
3/ Select columns [Pto] and [Center]
4/ Go to the Transform tab
5/ Fill Up
6/ Fill Down

Some Power Query learning resources in this PDF