Copy Formulas Down When Refreshing a Power BI Query

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132
I have a power BI query that I run and pull into excel to review billing data. I have additional columns added to the end of the query that are vlookup formulas to pull data from another tab. When I refresh the data if there are more rows added it is not copying the vlookup formulas down.

Is there a way to set the properties so that when it refreshes it knows to copy the formula from the row above for any new rows?
If not, I was going to add a macro for the refresh and at the end add a copy function but to do that I would like it to find the last populated cell in column AW in order to tell it to copy the formula down to all remaining rows. Is there an easy way to do this?

Any/All feedback would be greatly appreciated
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
If I try your scenario, then the additional columns are automatically adjusted to the new table size (this works with additional rows, and also with less rows).
I suspect that a prerequisite is that you have a uniform formula for the entire table column.
Can you verify and possibly adjust your formulas so this prerequisite is met?

Maybe there is a difference between Excel versions: mine is Excel 2016, version 1709 Build 8528.2139 (via Office 365 subscription).

Just to avoid misunderstandings:
1. I guess you mean "Power Query" (without "BI"), so everything is happening in 1 Excel workbook?
2. With "columns added to the end of the query" you mean: "columns added to the end of the table that is output from the query"?
 
Upvote 0
If I try your scenario, then the additional columns are automatically adjusted to the new table size (this works with additional rows, and also with less rows).
I suspect that a prerequisite is that you have a uniform formula for the entire table column.
Can you verify and possibly adjust your formulas so this prerequisite is met?

Maybe there is a difference between Excel versions: mine is Excel 2016, version 1709 Build 8528.2139 (via Office 365 subscription).

Just to avoid misunderstandings:
1. I guess you mean "Power Query" (without "BI"), so everything is happening in 1 Excel workbook?
2. With "columns added to the end of the query" you mean: "columns added to the end of the table that is output from the query"?


You are correct, it is power query
I am using excel 2013 and everything is in one workbook
correct, at the end of the table created by the query I have added the columns that contain the vlookup I am using

I am trying to get the formula to be uniform for the whole column but it doesn't seem to want to do that when I refresh with new data. If rows are added it doesn't carry the formula down in the new rows.
 
Upvote 0
Maybe you can double check if your additional columns are directly adjacent to your table (i.e. they become actually part of your table from Power Query).

Otherwise, for a simple macro solution, you can check the answer in this post on StackOverflow.
 
Upvote 0
Maybe you can double check if your additional columns are directly adjacent to your table (i.e. they become actually part of your table from Power Query).

Otherwise, for a simple macro solution, you can check the answer in this post on StackOverflow.

Thanks for you help! I figured out my issue. After I added the columns I merged the header cells across multiple columns. When I undid this and updated my formulas it resumed the autofill of the formula for all new rows added.
 
Upvote 0
Glad your issue is solved. I guess your header cells weren't actually part of the table as it is not even possible to merge table headers (at least the merge option is greyed out with me for table header cells).

guestaccess.aspx
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top