Hello,
I'm new to Power BI but have a model that has a Calendar and use the Date field to create relationships between all my other models.
Calendar =
ADDCOLUMNS (
CALENDAR ( "1-jan-2013", "31-dec-2025" ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"Date Days From Now", NOW()-[Date])
The problem i'm having is that when i try to connect to the "Lead" salesforce object i'm unable to perform any date based queries and it never reports an error. I'm doing the same thing i would have normally done which is form a relationship between the Date and the CreatedDate but it never seems to work. When i connect to the Salesforce Report: # of Leads and do the exact same thing it does work and all the queries start showing correct results.
Here is the query for Lead:
let
Source = Salesforce.Data(),
Lead1 = Source{[Name="Lead"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Lead1,{"Email", "CreatedDate"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"CreatedDate", "Email"})
in
#"Reordered Columns"
Here is the query for the Saesforce Report
let
Source = Salesforce.Reports(),
#"00O610000014MWSEA2" = Source{[Name="00O610000014MWSEA2"]}[Data]
in
#"00O610000014MWSEA2"
Whenever i try to transform the CreatedDate in the Lead Object (trying to turn the DateTime to just a Date) i get
DataSource.Error: Non-grouped query that uses overall aggregate functions cannot also use LIMIT
Details:
List
Again i'm not an expert at this but there seems to be something weird happening when i try to form a relationship between the date.
I could also be doing things completly wrong so if that's the case please let me know.
All the best,
