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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dplandry
Regular Visitor

EXTRACT DATA FROM TABLES BEFORE ARCHIVE

Hey guys

 

I'm pretty new to Power BI, but am learning fast and loving it.   I am trying to build a report on deleviry effeciency, but the main table I need to use to pull data is archived such that only the recent 6 months of data ramains in the table....beyond that it's moved to another archive table.  I'm having issues getting the data from the sales orders to pull from each table.  Is there a way to extra the data from the current data into a new table before it's archived and removed?

 

Thanks

Dwaye

 

4 REPLIES 4

Hi!

 

What troubles do you face when you extract the data from both, the "current" and the "archive" table?

This should work pretty straight forward.

 

I am not aware of a possibility to extract data (from your "current" table) and "hold" it in PowerBI without getting it refreshed.

Markus Ehrenmueller-Jensen
@MEhrenmueller

I'm having a problem linking the current table to the archive table. In the order table, the sales order appears once. The archive table holds sales order lines so the sales order appears more than once so I'm having issues linking them. If I use the main table, I only get 6 months of sales orders.


@dplandry wrote:
I'm having a problem linking the current table to the archive table. In the order table, the sales order appears once. The archive table holds sales order lines so the sales order appears more than once so I'm having issues linking them. If I use the main table, I only get 6 months of sales orders.

 

Hi @dplandry,

 

Do you mean there is a common field sales order column between the archive table and order table, if that is a case, you can create a relationship between the archive table and order table with many to one. See: Create and manage relationships in Power BI Desktop.

 

But if you want to add additional rows of data from archive table to the main table, you can open Query Editor and use the feature Append Queries.

 

a1.PNG

 

If I misunderstand the issue, please share some screenshots about the sample data and expected results for our analysis.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Can you support me with more details about the (useful) columns of both tables?

 

From the knowledge I have so far about your case, it should be possible to GROUP the archive table when you load it via Power Query, so you get down to the expected number of rows.PowerQuery Group.jpg

Markus Ehrenmueller-Jensen
@MEhrenmueller

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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