date_key column you mentioned, which is formatted as a whole number and interpreted as Text, presumably represents dates in a format like
YYYYMMDD (for example
20230829 for August 29, 2023). You wish to custom format this column to make it appear like a typical date format.
You will be using DAX (Data Analysis Expressions) to achieve this custom formatting. You don't need Power Query for this task.
Create a new calculated column in your table with the following DAX formula:
CustomDate = FORMAT(DATE(VALUE(LEFT([date_key], 4)), // Year VALUE(MID([date_key], 5, 2)), // Month VALUE(RIGHT([date_key], 2))), // Day "MM/DD/YYYY")
This formula breaks down the whole number into its year, month, and day components, then uses the
FORMAT function to represent it in the "MM/DD/YYYY" format.
In case you're asking about the 'Format String' option that's present in the modeling tab, that's for direct custom formatting. But given your requirement, the transformation of a YYYYMMDD integer into an actual date format is best achieved with DAX as described above.
In SSAS Tabular, data types are inferred during the import process. You don't change data types like you do in Power BI Desktop. If your column data type has been inferred incorrectly, you often have to adjust it at the source or during the data import process.
While the main intent of DAX is for calculations, you can create a new calculated column representing your original column as text. You can do this using the
`DateKeyAsText = FORMAT([date_key], "")`
This formula will convert the
date_key into a text representation in a new column.
Once you have your data in SSAS Tabular, you can further adjust column properties and formats using Tabular Model Scripting Language (TMSL) or Tabular Object Model (TOM), but these are more advanced topics and might be beyond what you're trying to achieve.
If you're trying to change the data type of an existing column to text in the Tabular Model, you would likely have to adjust this at the data source level or during your data loading process. Remember, SSAS Tabular is optimized for fast performance, and its design assumes that much of the data shaping and transformation happens before data gets into the model.