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.
Hello,
I have a task to reconnect a table with the Date column to BigQuery in my existing report. The Date column has a DATETIME type.
My Power Query code looks like this for filtering values for the last 6 months:
let Source = GoogleBigQuery.Database(null), BQDatabase = Source{[Name=bq_project_name]}[Data], BQSchema = BQDatabase{[Name=bq_db_name,Kind="Schema"]}[Data], BQView = BQSchema{[Name=bq_view,Kind="Table"]}[Data], Filtered_Last6Mo = Table.SelectRows(BQView, each Date.IsInPreviousNMonths([DateKey], 6)), in Filtered_Last6Mo
When I do apply these queries, I can see the table with data on the Power Query side. After I click on "Close & Apply", I got the following error on the Power BI side in a few minutes:
OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: No matching signature for operator >= for argument types: DATETIME, TIMESTAMP. Supported signature: ANY >= ANY at [23:8].
It used to work when I used MS SQL as a data source for the table.
Can anyone help me with this issue?
Thanks in advance!
Solved! Go to Solution.
I don't have BigQuery to play with here, but if the field from BIgQuery is DateTime, you should try converting it to Date only, then use the Date.IsInPreviousNMonths. That won't fold once you do it I am 99% certian.
The other way you could try it without switching the data type is this:
See if that will run. That will fold as Power Query will pass a constant of the value for varStartDate, which right now is April 1, 2020 at 12:00am.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello,
I have a task to reconnect a table with the Date column to BigQuery in my existing report. The Date column has a DATETIME type.
My Power Query code looks like this for filtering values for the last 6 monthes:
let
Source = GoogleBigQuery.Database(null),
BQDatabase = Source{[Name=bq_project_name]}[Data],
BQSchema = BQDatabase{[Name=bq_db_name,Kind="Schema"]}[Data],
BQView = BQSchema{[Name=bq_view,Kind="Table"]}[Data],
Filtered_Last6Mo = Table.SelectRows(BQView, each Date.IsInPreviousNMonths([DateKey], 6)),
in
Filtered_Last6Mo
When I do apply these queries, I can see the table with data on the Power Query side. After I click on "Close & Apply", I got the following error on the Power BI side in a few minutes:
OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: No matching signature for operator >= for argument types: DATETIME, TIMESTAMP. Supported signature: ANY >= ANY at [23:8].
Can anyone help me with this issue?
Thanks in advance!
@mdor , Not very clear.
You can have 6 month of data
example
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
It used to used work before when I used the MS SQL server. But with the BigQuery, filtering stopped working for me. Also, it's not a solution for me because it should be done on the Power Query side, not with DAX.
I don't have BigQuery to play with here, but if the field from BIgQuery is DateTime, you should try converting it to Date only, then use the Date.IsInPreviousNMonths. That won't fold once you do it I am 99% certian.
The other way you could try it without switching the data type is this:
See if that will run. That will fold as Power Query will pass a constant of the value for varStartDate, which right now is April 1, 2020 at 12:00am.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.