Morning all - seriously hoping that someone can help with a PQ issue that is causing me a lot of headaches...
I run a SharePoint site containing >5000 files, and each file contains custom Properties that inform me of the status of each respective file. Every day I run a PQ from a separate file that queries and compiles the properties from each of these files and builds reports from the back of it. As the data I need is saved under custom properties, this come out as a large chunk of text that I then apply delimiters and pivots to in order to strip out into the consistent columns of data. All this works perfectly every day :-)
The issue comes when handling columns with dates. These columns usually contain dates (dd/mm/yyyy) but they also contain some text responses which will vary depending on the circumstances.
As it stands, I currently output the column as text (shown on the attachment as Current output) which preserves the whole dataset (both dates and text responses), but this means that the date dropdown filter on the output doesn't group dates by month/year etc. This inability to quickly and reliably filter by date is now causing a lot of problems.
I could simply exclude any non date values (shown on the attachment as Alternative output) but I lose sight of the various text reponses.
Alternatively I could use a simple If statement in the workbook (shown on the attachment as Alternative formula driven output). This however causes problems because the number of rows changes every day, some of the reporting is done as connection only so doesn't utilise any workbook, and now my data contains formulas rather than values which may cause problems further down the line. I really want to keep this all within PQ because it is so much more reliable and stable, especially when I need to make changes in the future.
I understand that a text value is a date column is a contradiction in terms, but does anyone know of a way I can run this entirely through PQ??
Bookmarks