Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JasonWills
New Member

Manipulating Data - Fill Down

Hi All,

 

first time posting here, so here goes...

 

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

 

2012UK + IRELANDJan
  Feb
  Mar
  Apr
  May
  Jun
  Jul
  Aug
  Sep
  Oct
  Nov
  Dec
  TOTAL
2013UK + IRELANDJan
  Feb
  Mar
  Apr
  May
  Jun
  Jul
  Aug
  Sep
  Oct
  Nov
  Dec
  TOTAL
2014UK + IRELANDJan
  Feb
  Mar
  Apr
  May
  Jun
  Jul
  Aug
  Sep
  Oct
  Nov
  Dec
  TOTAL
2015UK + IRELANDJan
  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

2 REPLIES 2
Sean
Community Champion
Community Champion

@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! Smiley Happy

Anonymous
Not applicable

@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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.