Power Query: I need to add a column to a table that has the value from another table

gheyman

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,342
Office Version
  1. 365
Platform
  1. Windows
I have a table named results. I need to find the Max date from that table from Field [Quote Date]. I know there is a number of way to get this Max value.

I have found the Max date from within that table and then made a reference of that table , did a grouping, deleted duplicate...... so that I had a table that only contained that Max value in one field that I named ResultsMaxDate

I then tried to add that to my other table ("Delta Results") by adding a column and using the formula =ResultsMaxDate() But that did not work, I got errors in every row of the new column

I tried making a list from the ResultMaxDate (that had only on row) and tried adding that to my "Delta Results" table by adding a new column =ResultsMaxDate() Again errors in each row of the new column

I tried creating a separate table on a tab called Result Max Date [ResultMaxDate] and used VBA to copy the Max date from the [Quote Date] and pasting it in. I then tried to use this tables data to create a new column in "Delta Results" - again nothing but errors in the new column for every row.

Can someone PLEASE lead me in the right direction? Please note that when the table "Results" is created I want to memorialize the Max Date - when I refresh the "Delta Results" I don't want to refresh the "Results" query at the same time. I tried creating a column in my "Delta Results" to get the Max Date from the "Results" [Quote Date] - but that did seem to work either. It make the "Results" query refresh and I lose the original Max date.

As stated, I want to memorialize the Max date when the "Results" query is first ran. Getting very discouraged, I didn't think this would be so difficult and my workbook is developed on the basis that I could gat that date and use it in later queries and VBA code.


I appreciate your time and help - thank you
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
One option for retaining the max date might be to change the refresh settings on the "Results" table. It sounds like you currently have it set to Refresh every time you use Data > Refresh All.

Right click on the query that creates the "Results" table, choose "Properties", then deselect the "Refresh this connection on Refresh All" under the Usage tab. This will give you control of the refresh schedule so that it only updates when you manually refresh.
 
Upvote 0
Thanks, but if in my table "Delta Results" I add the Max Date by using List.Max(Results[Quote Date]) it automatically is refreshing the Results table when I refresh the "Delta Results"

Plus I still dont know why it will not allow me to create a new column and have that column equal the list I created ResultsMaxDate
= ResultsMaxDate()

It results in an error in each row
 
Upvote 0
Have you clicked on the "error" in the query display? It will open a new query that shows you why the error is happening.

In a lot of cases it is just formatting.
 
Upvote 0
Yes, it does say formatting. But I am not sure what this exactly means. The table I am getting the data from has the Max Date formatted as a Date. Do i need to change that?
 
Upvote 0
Hard to figure this out without seeing your query and looking at the error.

When you start a query, there is usually a "Change Type" step automatically created. It should be one of the first few steps. Make sure that the data in the Date column is importing correctly. Is the date column properly formatted as date? Are there errors?

When are you getting the error? Is the error happening early in the query steps, or does the error occur as soon as you request the Max date? That might tell you if you have issues as the data comes in or if your issues are happening within the max date command.

If the data is fine (no errors) prior to the max date step, try this:

1. Sort "Date" column by descending (this will list the newest date first)
2. Use "Keep Rows" under the home tab to keep the first row (which will have the most recent, or max date)

This is a less efficient method, but may help you solve your issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Return most recent data - multiple criteria
Replies
0
Views
216
Exceloway
E
jimrward
  • Question
Add column with Max date from another table
Replies
4
Views
215
jimrward
jimrward
C
  • Question
Transpose a table using power query
Replies
1
Views
164
RoryA
RoryA
D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
546
Matt Allington
M
M
  • Question
"The connection for this pivottable has been deleted" in Power Pivot
Replies
0
Views
106
Mr_Stern
M

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top