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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Rayyy12
Regular Visitor

How to work with datasets that need to be updated on a monthly basis?

I'm trying to create an analysis by retrieving our data from Analysis for Office and uploading it on Power BI. The issue is my source data layout would require couple of changes when uploading it on power query (for instance, removing columns and unnecessary rows). I tried updating the data for the new month however the update was not reflected on the dashboard. This data is sorted on a monthly basis and every month may have new rows added.

 

Does anyone have suggestion on how I could make this so efficient and easy to update on a monthly basis?

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Rayyy12 

 

First, you need to understand the different storage modes and dataset types that Power BI supports, and how they affect the data refresh process. You can choose between importing data into Power BI or querying the data directly from the source. Import mode datasets require a source data refresh, while DirectQuery, LiveConnect, and push mode datasets do not. Data refresh in Power BI - Power BI | Microsoft Learn

 

Second, you need to know the different ways to refresh your data in Power BI, either manually or automatically. You can use the Refresh now option in Power BI Service or Power BI Desktop to refresh your data on demand. You can also use the Schedule refresh option in Power BI Service to set up a refresh schedule for your dataset. If your data sources are on-premises, you need to install and run a gateway for Power BI to connect and refresh the dataset. 

Configure scheduled refresh - Power BI | Microsoft Learn

 

Third, you need to choose the right method to export your data from Analysis for Office to Power BI. You can use the Analyze in Excel feature to create an Excel workbook containing the entire dataset for a specific Power BI report and analyze it using PivotTables, Pivot Charts, and other Excel features. You can also use the Export to Excel with live connection feature to create an Excel workbook containing a specific table or visual from a Power BI report and analyze it using Excel tables. Both methods allow you to refresh the data in Excel directly from Power BI.

Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-zhangti
Community Support
Community Support

Hi, @Rayyy12 

 

First, you need to understand the different storage modes and dataset types that Power BI supports, and how they affect the data refresh process. You can choose between importing data into Power BI or querying the data directly from the source. Import mode datasets require a source data refresh, while DirectQuery, LiveConnect, and push mode datasets do not. Data refresh in Power BI - Power BI | Microsoft Learn

 

Second, you need to know the different ways to refresh your data in Power BI, either manually or automatically. You can use the Refresh now option in Power BI Service or Power BI Desktop to refresh your data on demand. You can also use the Schedule refresh option in Power BI Service to set up a refresh schedule for your dataset. If your data sources are on-premises, you need to install and run a gateway for Power BI to connect and refresh the dataset. 

Configure scheduled refresh - Power BI | Microsoft Learn

 

Third, you need to choose the right method to export your data from Analysis for Office to Power BI. You can use the Analyze in Excel feature to create an Excel workbook containing the entire dataset for a specific Power BI report and analyze it using PivotTables, Pivot Charts, and other Excel features. You can also use the Export to Excel with live connection feature to create an Excel workbook containing a specific table or visual from a Power BI report and analyze it using Excel tables. Both methods allow you to refresh the data in Excel directly from Power BI.

Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.