Converting Int to Date in PQ wuth Query Folding

[Anonymous]

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

[Vijay_A_Verma]

Can you show some values of your Calendar Key?

[Anonymous]

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 🙂

[v-jingzhang]

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.

[BA_Pete]

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

[BA_Pete]

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