Forum Discussion

rachaelwalker's avatar
rachaelwalker
Resolver III
4 years ago
Solved

Pulling Dates from another table with IF statement

I need help with a formula. I have two tables. My Quote table needs to pull the Expected_Close_Date from my Opportunity table based on matching Opportunity IDs. It has a one-to-many relationship beca...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi rachaelwalker ,

    How about something like:

    Expected_Close_Date (in Quote Table) =
VAR _Expected_Close_Date =
    LOOKUPVALUE (
        'Opportunity Table'[Expected_Close_Date],
        'Opportunity Table'[OppID], 'Quote Table'[OppID]
    )
VAR _Result =
    IF (
        ISBLANK ( _Expected_Close_Date ),
        'Opportunity Table'[insert some date field here],
        _Expected_Close_Date
    )
RETURN
    _Result



KNP's avatar
KNP
Super User
4 years ago

If you want a Power Query solution...

 

Opportunity

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUVKK1YlWMgIyjEz1DY30jQyMDMFCxiAhAxQhE7DyWAA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [OppID = _t, ExpectedCloseDate = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"OppID", Int64.Type}, {"ExpectedCloseDate", type date}}
  )
in
  #"Changed Type"

 

Quote with merged Opportunity data and NewDate added

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSQWB9QyN9IwMjQ6VYnWglI6ioEYaMMVTGGEPGBCpjgioTCwA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [QuoteID = _t, ProductID = _t, OppID = _t, Date = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"QuoteID", Int64.Type}, {"ProductID", Int64.Type}, {"OppID", Int64.Type}, {"Date", type date}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Changed Type",
    {"OppID"},
    Opportunity,
    {"OppID"},
    "Opportunity",
    JoinKind.LeftOuter
  ),
  #"Expanded Opportunity" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Opportunity",
    {"ExpectedCloseDate"},
    {"ExpectedCloseDate"}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Expanded Opportunity",
    "NewDate",
    each if [ExpectedCloseDate] = null then [Date] else [ExpectedCloseDate],
    type date
  )
in
  #"Added Custom"

 

Paste each of these into the advanced editor of blank queries if you prefer to visulise it that way.

I hope this helps.