Creating Composite Primary Key

[Stinkys]

Hi all,

I am trying to understand the best method for creating a composite primary key where one of the columns is a date. I have several columns that when combined create my unique record - int, text, date. In lakehouse my date is stored in yyyy-mm-dd however when working with Text.Combine in Dataflow Gen2 it outputs as dd/mm/yyyy.

Anyone have a guide on how to best implement a composite key that will respect the original database date format so that my composite key will be along the lines of "6789-ABC123-2024-11-05"? I feel like PySpark might be the best answer here in generating keys prior to or after the DFG2 process.

[Stinkys]

Thanks for the reply Fabian. I was hoping there was a cleaner method for this, as it seems counter-intuitive to go from db date (db) to formatted date (dfg2 initial load) to db date (for merge) then back to clean formatted date for data warehouse. Would be nice if I could work with that raw db date value in dfg2.

[FabianSchut · Accepted solution]

Hi, in DataFlow Gen2, you first need to convert the date format to a string format where you have defined the string format yourself. An example of a query will look like this:

Table.TransformColumns(#"Changed column type", {{"Column1", each Date.ToText(Date.From(_),"yyyy-MM-dd"), type text}})

If you want to achieve this with Python use something like

.strftime('%Y-%m-%d')