Query Editor - Fill a Cell Value with the Min of a Column Value of another Table

[trovisco]

Hello Everyone,

I would Like to fill a Cell Value in My query Table with the MIN Value of a Column of another Table, as shown below..

This is the Source Table where I want to get the Value from:

1. Is this Possible to do directly in the Query Editor? 

At the moment I am doing it outside the Query Editor, by adding a Calculated Column with a Formula, but it would be much better if it was done directly in Query Editor

My Final Objective is to then be able to plot in the same Chart the Hours by Date, but I don't have a common Date Column for both data... Please see the images below:

2. What would be a good solution to have a same common date axis for both Data?

I hope it was everything Clear,

Thanks for your support in Advance!

[MFelix]

Hi @trovisco ,

Add a custom column to your first table with the following syntax:

if [Input Milestones Dates]= null then List.Min("Report SAP DAB"[Date Created])else [Input Milestones Dates]

Then delete the Input Milestones Dates and rename the new column to Input Milestones Dates

To have a common axis you need to create a calendar table and relate them with both tables.

[trovisco]

Hello Miguel! Thanks for the quick reply!

I impletemented the code you've mentioned, but I am getting the following error:

Any Idea how to solve it? 

Thank you in advance!

[MFelix]

Hi @trovisco ,

Check if the Date Created is a text or a Date field, you need to have it as a date field.

[trovisco]

I have checked and it is formated as Date 😞

Any other ideas?

[MFelix]

Hi  @trovisco ,

Try the following:

if [Input Milestones Dates]= null then Date.FromText(List.Min("Report SAP DAB"[Date Created]))else [Input Milestones Dates]

[trovisco]

Hi @MFelix , 

Unfortunately it is still returning an error. I've sent you the PBI file. 

[MFelix · Accepted solution]

Hi @trovisco ,

Looking at your file the issue is because of the way you have refered to the table.

In M Language when you have non continuous naming (wiht a space on it) you need to have the following syntax:

#"Name Discontinued"

the # is an operator that determines the variable name in this case the table name within the "". So basically in your syntax that is what is missing if you use the following code it will work properly based on the test of the file you have send out to me.

if [Data Inicial]= null then List.Min(#"Event Duration Prediction"[Date]) else [Data Inicial]

No need to use the Date.FromText syntax has you had also on your calculation.

[trovisco]

It works now 🙂 Thanks