Hi,
I'm experiencing an issue with PowerQuery's Table.SelectRows
In short: My database consists of imported files in folders. Every week a new files are added to these folders, but i dont need data over half a year old.
Im filtering out the oldest data by applying a "half year check": Adding 26 weeks to "the date" in the file, then see if its positive in comparison with the Sunday prior to today.
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.AddWeeks([Created On], 26) >= Date.StartOfWeek(DateTime.LocalNow(), Day.Sunday))
The issue:
- For several tables in the same database, this works like a charm
- For one specific table the rows are not filtered
- The preview, when having written the code, shows the desired result...
- ...but when you apply and close, the refresh is triggered but desired effect is not taking shape
- I've checked whether there are differences between the working tables and this one, couldn't find any
- The code itself seems to be allright (same as above, except the date column has a different name. I checked spelling and paid attention to case sensitivity
- Ive searched other posts, on and off of this forum, no solutions matched with my situation
For reference, this is the complete code for the table it wont work for:
=====
let
Source = Folder.Files("C:\Users\ralla008\Documents\Repeated calls\Week files"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Handling Attempt Hint", Int64.Type}, {"Tenant Name", type text}, {"Interaction ID", Int64.Type}, {"ID", Int64.Type}, {"Interaction Type", type text}, {"Handling Attempt Start", type datetime}, {"callEnd", type datetime}, {"Start Timestamp", type datetime}, {"CallDate", Int64.Type}, {"Duration", Int64.Type}, {"Media Type", type text}, {"From", type any}, {"To", type any}, {"To outbound", type any}, {"GUID", type text}, {"Last IVR", type text}, {"Last Queue", type text}, {"Last VQueue", type text}, {"callAgt", type text}, {"Handling Resource Type", type text}, {"Response Time", Int64.Type}, {"Queue Time", Int64.Type}, {"Routing Point Time", Int64.Type}, {"Total Duration", Int64.Type}, {"Customer Engage Time", Int64.Type}, {"Customer Hold Time", Int64.Type}, {"Customer Handle Time", Int64.Type}, {"Customer Alert Time", Int64.Type}, {"Customer Dial Time", Int64.Type}, {"Customer Wrap Time", Int64.Type}, {"Conference Initiated Time", Int64.Type}, {"Conference Received Time", Int64.Type}, {"Routing Target", type text}, {"Routing Target Type", type text}, {"Routing Target Selected", type text}, {"Customer ID", type text}, {"Service Type", type text}, {"Service Subtype", type text}, {"Customer Segment", type text}, {"Business Result", type text}, {"Resource State", type text}, {"Technical Result", type text}, {"Technical Result Reason", type text}, {"Technical Result Resource Role", type text}, {"Technical Result Role Reason", type text}, {"Active", Int64.Type}, {"Stop Action", Int64.Type}, {"Call Type", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.AddWeeks([callEnd], 26) >= Date.StartOfWeek(DateTime.LocalNow(), Day.Sunday))
in
#"Filtered Rows"
=====
Please advise 🙂
Kind regards,
Ramon
EDIT: Despite the fact that after applying the code, the table was definitely 1) incorrect and 2) workable, being able to sort, add columns, manual filter etc, somehow the code did have some effect. When closing and reopening the .pbix, the table had the desired data in it. I thought of deleting this topic, but maybe leaving it up for people to see that "have you tried turning it off and on again" in these situations actually could prove to be your solution 🙂
Thanks for everyone who already spent time reading...
