Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Converting Int to Date in PQ wuth Query Folding

Hello Everyone

 

I have a Calendar Key that I need to convert into a Date Column. I've done this with the below Power Query Script but this breaks the query folding back to our SQL Server source.

 

= Table.AddColumn(#"Filtered Rows", "Date", each Date.From(Text.From([Calendar Key], "en-GB")), type date)

 

I know in an ideal world, I'd get this changed in SQL and just pull a date from source but I can't in this instance and need to generate a Date from the Date Key that will fold back

 

Any help would be great appreicated

 

Thanks

Need Help

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

     

    Is this date key you want to change in a fact table?

    If it is, you could just recreate the date key in your calendar table ( Date.ToText([date], "yyyMMdd") ) and relate to the fact on dateKey.

     

    Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello

     

    The Date Key is in the Fact Table and I need to create a Date within the fact table. I have a calendar table and that already has a key but I need to create an inactive join to another table that has a calculation date column in it that I can't change into a Date Key so I need to Add a Date into the Fact table if that makes sense 🙂

     

    • BA_Pete's avatar
      BA_Pete
      Super User
      4 years ago

       

      Fair enough.

      Can you do the conversion to date in your fact table or add a date key column for your [calculation date] column via DAX on the report side instead of trying to hammer it into Power Query?

       

      Pete

    • v-jingzhang's avatar
      v-jingzhang
      Community Support
      4 years ago

      Hi Anonymous

       

      You can add a Date column with DAX. This will not influence the query folding. For example, 

      Date = DATE(INT([DateKey]/10000),INT(MOD([DateKey],10000)/100),MOD([DateKey],100))

       

      Best Regards,
      Community Support Team _ Jing
      If this post helps, please Accept it as Solution to help other members find it.