incremental refresh based on a dates stored with the YYYYMMDD INT format

[Marc-Alexis]

Hello everyone,

I am trying to activate the incremental refresh on a table “FMT_Reporting_V2V_GuestHistoryDaily”  based on a dates saved in a column “Revenue date” in the format YYYYMMDD as whole numbers, stored and imported from a MySQL database with the INT format YYYYMMDD.

I have set RangeStart as following:

• Type: Date/Time
• Required: unchecked
• Suggested Values: Any value
• Current value: 01/01/2017 00:00:00

And RangeEnd as following:

• Type: Date/Time
• Required: unchecked
• Suggested Values: Any value
• Current value: 12/12/2050 00:00:00

Here is my current query:

let

    Source = MySQL.Database("10.203.1.116", "FMT_Reporting", [ReturnSingleDatabase=true]),

    FMT_Reporting_V2V_GuestHistoryDaily = Source{[Schema="FMT_Reporting",Item="V2V_GuestHistoryDaily"]}[Data],

    #"Filtered Rows" = Table.SelectRows(#"FMT_Reporting_V2V_GuestHistoryDaily", each each [Revenue date] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [Revenue date] <= Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd)),

    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rev_Room_Net_LC", "Rev_FB_Net_LC", "Rev_BQT_Net_LC", "Rev_SPA_Net_LC", "Rev_Ski_Net_LC", "Rev_Other_Net_LC"})

in

    #"Removed Columns"

When trying to apply the query I get into the following error message:

Apply query changes

FMT_Reporting V2V_GuestHistoryDaily

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] We cannot convert a value of type Function to type Logical.. '.

Any idea on what needs to be changed?

Thanks for you help!

[v-xuding-msft · Accepted solution]

Hi @Marc-Alexis ,

In the step of "Filtered Rows", there is an extra "each". Please remove it to have a try.

= Table.SelectRows(#"FMT_Reporting_V2V_GuestHistoryDaily", each [Revenue date] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [Revenue date] <= Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))

let

    Source = MySQL.Database("10.203.1.116", "FMT_Reporting", [ReturnSingleDatabase=true]),

    FMT_Reporting_V2V_GuestHistoryDaily = Source{[Schema="FMT_Reporting",Item="V2V_GuestHistoryDaily"]}[Data],

    #"Filtered Rows" = Table.SelectRows(#"FMT_Reporting_V2V_GuestHistoryDaily", each [Revenue date] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [Revenue date] <= Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd)),

    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rev_Room_Net_LC", "Rev_FB_Net_LC", "Rev_BQT_Net_LC", "Rev_SPA_Net_LC", "Rev_Ski_Net_LC", "Rev_Other_Net_LC"})

in

    #"Removed Columns"

[Marc-Alexis]

Hi Xue! Thanks for this. It was indeed the issue. Aprreciated!

[amitchandak]

@Marc-Alexis , refer if these can help

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-troubleshooting-refresh-scenarios

https://data-flair.training/blogs/power-bi-troubleshooting/