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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Saxon10
Post Prodigy
Post Prodigy

Power query automatically refresh

I have one Excel file that Excel file contain one sheet which is called "Sales". In Sales sheet contain 3 column. In Sales sheet column 2 and 3 has data and column 1 always is empty.

 
I created new sheet which is called "Sales 1" within the same Exce file based on the sales sheet by using power query also I removed blanks columns and I kept other two columns only . 
 
Whenever the sales date is refreshing then sales 1 data automatically refresh itself? 
 
Can you please advise 
1 ACCEPTED SOLUTION

@Saxon10 

Please check the attached Excel File. When you update data in sale sheet, the table in sale1 will be refreshed. I also provided a button to refresh if you need to use it.  

https://1drv.ms/x/s!AmoScH5srsIYgZItGfJ7MdKoZVtEEw?e=UxbiRO


 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
Fowmy
Super User
Super User

@Saxon10 

You are connecting to a single Excel file where the sheets Sale and Sale1 exist. If you have a single query that references the two sheets, you get the updated data from both sheets. If the different queries are made for each sheet, you need to refresh separately. It also depends on how you have set up the connection.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thnaks for your reply and advise. Yes, I am connecting single in same file with two different sheet. The difference in-between two sheet (sales and sales1) is blanks column (Item).

 

In order to get the automatic refresh in sales1 sheet based on the sales sheet without opening Excel file or time setting. What kind of connection or setting need to be setup in order to get the automatic refresh.   

 

Example: If I add data in sales sheet then it's automatically need to be replicate in Sales1 sheet.

 

Could you please provide artical or snapshot more understanding the logic.

 

Sales1

 

Saxon10_0-1620458114547.png

 

Sales

 

Saxon10_1-1620458190166.png

 

 

@Saxon10 

 

I got it, so you need to automatically refresh the data in Sales1 sheet when you enter data in Sales right?

 

you need to include a small piece of VBA code in your Exfel file. 

you attach your here by saving the file on google drive or OneDrive the share the link here. 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi,

 

Thanks for your reply.

 

Yes, that's right. 

 

Note: In sales sheet I have 3 columns and sales1 sheet I have two columns. In sales sheet Item columns has always blanks.

 

The macro will take care of auto refresh or need to be perform any addtional steps in query setting/properties in order to complete auto refresh?

 

Thank you.

 

 

Herewith attached the Excel file for your reference. 

 

https://www.dropbox.com/scl/fi/llquz0k7lty9meynvg6y1/DATA-PQ.xlsx?dl=0&rlkey=czn0s84x9cv4jwfp4gpass6...

 

 

@Saxon10 

Please check the attached Excel File. When you update data in sale sheet, the table in sale1 will be refreshed. I also provided a button to refresh if you need to use it.  

https://1drv.ms/x/s!AmoScH5srsIYgZItGfJ7MdKoZVtEEw?e=UxbiRO


 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

thank you so much for your help and solution. It's working well. 

I am using old Excel version so unable to attached file.

 

Sales1

 

COUNTRYDATE

UAE 05/05/2021 00:00
AUS 02/04/2021 00:00
INDIA 02/04/2021 00:00
TML 05/05/2021 00:00
SRI 02/04/2021 00:00
PAK 02/04/2021 00:00
INDIA 23/03/2021 00:00
CAND 02/04/2021 00:00
US 02/04/2021 00:00
NEWL 02/04/2021 00:00
UK 02/04/2021 00:00


Sales

ITEMCOUNTRYDATE


INDIA 02/04/2021

PAK 02/04/2021

US 02/04/2021

UK 02/04/2021

AUS 02/04/2021

SRI 02/04/2021

CAND 02/04/2021

NEWL 02/04/2021

INDIA 23/03/2021

UAE 05/05/2021

TML 05/05/2021

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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