Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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] ) )
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
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] ) )
Hi,
Could you please suggest me how to achive it using power query instead of dax?
Thanks,
Amit
@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?
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 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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
61 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |