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 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:
And RangeEnd as following:
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!
Solved! Go to 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"
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"
Hi Xue! Thanks for this. It was indeed the issue. Aprreciated!
@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/
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 |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |