Manipulating Data - Fill Down

[JasonWills]

Hi All,

first time posting here, so here goes...

I have some data from an excel sheet that looks like this:

2012	UK + IRELAND	Jan
		Feb
		Mar
		Apr
		May
		Jun
		Jul
		Aug
		Sep
		Oct
		Nov
		Dec
		TOTAL
2013	UK + IRELAND	Jan
		Feb
		Mar
		Apr
		May
		Jun
		Jul
		Aug
		Sep
		Oct
		Nov
		Dec
		TOTAL
2014	UK + IRELAND	Jan
		Feb
		Mar
		Apr
		May
		Jun
		Jul
		Aug
		Sep
		Oct
		Nov
		Dec
		TOTAL
2015	UK + IRELAND	Jan
		Feb
		Mar
		Apr
		May
		Jun
		Jul
		Aug
		Sep
		Oct
		Nov
		Dec
		TOTAL

I'm trying to get column A (the Year) to fill down until it sees data in the column, so 2012 will be filled down until 2013, then 2013 will be filled down until 2014 etc...

Is there any way in which this can be done, i've battled for a while, but am not really getting anywhere.

Thanks for any help in advance.

Regards

Jason

[Sean]

@JasonWillsyou are working with a Pivot Table that's in Tabular Form

Is this a 1) functional Pivot Table or has someone copied the result and given you the 2) copy?

1) If its a real functional Pivot Table you can do the steps below and then copy the result if that's what you want to import?

(don't know why you want to do this though - and not import the sources of the Pivot Table instead???)

Go Back in EXCEL => click inside the Pivot Table to get the PivotTable Tools tabs => go to the Design tab

in the Layout group of buttons on the left side of the screen => click the Report Layout button (drop-down)

select Repeat All Item Labels

The last step would be in the same group of buttons => click Subtotals button => Do Not Show Subtotals

You can do the same for Total Rows/Columns if necessary

Now you can copy the results and import

2) If this is a Copy of a Pivot Table - why don't you just click in the Cell under 2012 type 2012 again and then drag till you reach 2013. Use the Auto-Fill this way under each "Value" just type it a second time and then drag to auto-fill

And delete the subtotal and total rows (and columns if any)

Hope this helps.

Good Luck!

[Anonymous]

@JasonWills,

Have you tried "Fill Down" when you Edit Queries - e.g. see this post: https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/

And it might help in the long run if you filter out the TOTAL rows for a cleaner data model.