Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |