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

[gheyman]

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

 

[SullyWYO]

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.

 

[gheyman]

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

 

[SullyWYO]

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.

 

[gheyman]

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?

 

[SullyWYO]

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.

 

[jaeiow]

Did you put an each before ResultMaxDate