cancel
Showing results for 
Search instead for 
Did you mean: 
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


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

View solution in original post

7 REPLIES 7
Fowmy
Super User IV
Super User IV

@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


Blog - Data Visualization

Website   YouTube    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


Blog - Data Visualization

Website   YouTube    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


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

View solution in original post

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors