Merge / Join Conditions and Clause >= or <=

[MarkDGaal]

I'm trying to duplicate this crossjoin I have in Tableau:

Note the Join Clauses of RFP/RFI Release Date >= Date (list of all possible date times starting from the begining of 2015)

and 

Proposal Submissiond Date < = Date

what is the best way to go about creating this condidtional join type using Merge Queries in PowerBI?

Thus far I've played around once bringing the entire Dates table over using condidtional columns and filtering, but not sure if that's working correctly.

[MarkDGaal]

Thus far I've tried:

let
    Source = #"SRC_crm_pipeline opportunities",
    #"Merged Queries" = Table.NestedJoin(Source,{"RFP/RFI_Release_Date"},#"SRC_crm_pipeline dates",{"Date"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Date", "Day_of_Week", "Day", "Month", "Year", "Holiday", "Workday"}, {"Date", "Day_of_Week", "Day", "Month", "Year", "Holiday", "Workday"}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "RFP/RFI >= and Proposal_Submission <=", each if(([#"RFP/RFI_Release_Date"]>=[Date] or null)) then "Yes"
else if(([#"Proposal_Submission_Date_©"]<=[Date] or null)) then "Yes"
else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"RFP/RFI >= and Proposal_Submission <="] = "Yes")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"RFP/RFI >= and Proposal_Submission <=", type text}})
in
    #"Changed Type"

but I believe this is different than the crossjoin as my final tables have vastly differing amounts of records 

[Eric_Zhang · Accepted solution]

@MarkDGaal

It looks that you're looking for a LEFT JOIN, if so, instead of using merger queries, I'd prefer DAX. Check if below sample helps.

Table =
NATURALLEFTOUTERJOIN (
    opportunities,
    FILTER (
        CROSSJOIN ( opportunities, dates ),
        opportunities[RFP/RFI Release Date ] >= dates[dates]
            && opportunities[Proposal Submissiond Date ] <= dates[dates]
    )
)

[amshukla]

Hi,

Could you please suggest me how to achive it using power query instead of dax?

Thanks,

Amit

[MarkDGaal]

@Eric_Zhang Oh...... now I get it.... you wanted me to create a calculated table in the data view. Haven't ever done that before. It works (number of records match the Tableau join), thank you@

Out of curiosity is there a Power Query / M Script Solution to this problem?

[MarkDGaal]

hummmm, are you suggesting that I simply create a new column inside the opportunities table?

If so I get the following error with that formula:

[Eric_Zhang]

@MarkDGaal

Can you post some sample data in your case? Even better you can upload a pbix.

[MarkDGaal]

@Eric_Zhang

sampledata as requested

Pbix

data

[MarkDGaal]

@Eric_Zhang Will do, sanatizing now; as a note I did find this similar thread/question on crossjoins with conditions using m-script/power query: Link answered by @ImkeF however they later got into some stuff that I don't quite understand about delimiting with a "|" and don't think I need for my situation.

I'll post an example file with an hour or so.