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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
sompolk
Frequent Visitor

How to collect result of previous query and stamp datetime of result to another table

Dear All,

I want to estimated cost of product month by month by get  previous date data and plus the forecast data of month in query, command as if date<previous date then use actual data else use forecast data. The report is refreshed one time a day every morming and be shown in bar chart. The problem is next day estimated cost changed due to previous actual data change every day such as 1 Feb 23 Est Cost= 100 $/ton, 2 Feb 23 Est Cost= 110 $/ton, 3 Feb 23 Est Cost=90 $/ton, I want to collect the estimated cost from each query day to another table and view in report to see the movement of estimated cost.  

sompolk_0-1677075786104.png

 

2 REPLIES 2
sompolk
Frequent Visitor

Dear DataSlayer ,

My file is Power BI Power Query Not Excel Power Query. Your suggestions not work. I couldn't do it from step1.
Do you have any ideas?
สกรีนช็อต 2023-03-02 172524.jpg

MAwwad
Super User
Super User

To collect the result of a previous query and stamp the datetime of the result to another table, you can use the following steps:

  1. Create your initial query to estimate the cost of the product. This query should use the "if date < previous date then use actual data else use forecast data" logic that you mentioned. The result of this query will be used to stamp the datetime to another table.
  2. In the Power Query Editor, go to the "Home" tab and click on "Close & Load To" to load the result of the initial query into a table.
  3. In the "Import Data" dialog box, choose "Only Create Connection" and then click "OK".
  4. In the Excel worksheet, go to the "Data" tab and click on "Queries & Connections" to open the "Workbook Connections" dialog box.
  5. Right-click on the connection that you just created and select "Load To...".
  6. In the "Load To" dialog box, choose "Only Create Connection" and then click "OK".
  7. In the Power Query Editor, create a new query to extract the result of the initial query and add the current datetime stamp. You can do this by using the following steps: a. Create a new custom column and enter the formula "=DateTime.LocalNow()". b. Rename the columns as needed. c. Remove any unnecessary columns.
  8. Close and load the result of the second query into a table in a new worksheet. This table will contain the datetime-stamped results of your initial query.
  9. Use the data in the second table to create your bar chart in the report.

By following these steps, you should be able to collect the estimated cost from each query day to another table and view the movement of estimated cost in your report.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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