Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 because quotes have multiple products that go under the same opp. I need to pull the Expected_Close_Date for all items on the quote when OppID = OppID
Opportunity Table (1)
OppID
Expected_Close_Date
Quote Table (many)
QuoteID
ProductID
OppID
This will at least get me started, but I am also finding some have blank dates. If the Expected_Close_Date is blank, how do I tell it to populate a native date field that is already in the Quote table. Thank you!
Solved! Go to Solution.
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
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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
This worked! Thank you
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |