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
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
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.