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 services database) presents the date in the form "1st January 2011" and unfortunately I cannot change this ... My aim is to only pull in data relating to the latest date available in this field. I have two problems that I suspect are connected when trying to achieve this.

The first one is that Power Query gives me an error saying that it can't parse this as a date (because of the Ordinal Indicators "st" "nd" "rd" "th") and defaults to treating the dates as Text. My solution to this was to do a simple find & replace on the ordinal indicator ("st" "nd" "rd" "th"), and then change type to date - this was a success. I then used the inbuilt options in the Power Query interface to filter to the latest date. Technically this solves my problem, but unfortunately, because of the table being quite large, and my solution being inefficient (i.e. I am performing operations on rows prior to discarding more than 99% of them), the performance is dreadful and it really takes a long time to pull through.

It made me think that I need to find a different way of dealing with the Ordinal Indicators ("st" "nd" "rd" "th") from the outset - otherwise I will always be performing a large number of transformations prior to filtering which is bad practice. Does anyone know how best to get this awkward formatting to be correctly interpretted as a date field by Power Query? 

Thanks so much for your help in advance.

Best wishes,
Laurence

Need Help
Tips and Tricks

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    When you were replacing the "th","st" how did you get it to not pull from the month?

    Example:

     

    August 25th 2019 --> Augu 25 2019

    lefinalzugzwang 

    • 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