Forum Discussion

lefinalzugzwang's avatar
lefinalzugzwang
Frequent Visitor
7 years ago

Dealing with dates containing ordinal indicators in Power Query

I have a table which contains millions of rows - basically for each date since 2011, each product in my company, and each customer we have, there is an attribute.  The data source (analysis servic...
Need Help
Tips and Tricks
lefinalzugzwang's avatar
lefinalzugzwang
Frequent Visitor
7 years ago

Hi Daniel, thanks for your reply. 

This is basically what I was doing ... it did give me another idea though, which was to try:

  • splitting "1st" from "January 2011"
  • change type of "January 2011" to Date
  • Filter to latest date (this will give me the latest months worth of data, so that the next few steps aren't being performed on as many rows)
  • only then remove the ordinal indicators and recombine to form "1 January 2011"
  • Filter to latest date (it was already filtered down to a one month period)

Unfortunately the performance doesn't seem to have improved by this. It takes much more time "evaluating", "loading data to model", and then takes more time per row (albeit with a lesser number of rows) - the net result is it is similarly slow to execute.

 

 This makes me wonder whether it is actually the filtering that is causing the performance issue? Is it normal for filtering rows by date to be such an intensive task? 

Thanks for your time.

Best wishes,
Laurence