Date format for direct query merged column

[visheshvsh]

in power bi, in direct query mode, I have created a merged column which have three columns. one of the column is a date column. but since its a merged column, the date column also act as text. so its formating is not dd/mm/yyyy its in yyyy-mm-dd format now. how to resolve this.

I have attached the merged column Reference. First part of the column is a number then the date part(highlighted) then a code.

If i give the same date column outside the merged column its showing in the correct format dd/mm/yyyy. Only inside the merged column its showing like yyyy-mm-dd. I need it in dd/mm/yyyy.

FORMAT function I have tried and its not working in direct query mode.

[visheshvsh · Accepted solution]

The above issue is resolved, I have used,

Reference Column :-

ValueDateFormatted = DAY(FundMovement[ValueDate]) & "/" & MONTH(FundMovement[ValueDate]) & "/" & YEAR(FundMovement[ValueDate])

Reference = FundMovement[FinancingFundNumber] &"  "& FundMovement[ValueDateFormatted] &"  "&FundMovement[OperationCode]

the merged column was coming in the expected manner. Eg : - 10001  26/12/2023  930

[v-nuoc-msft]

Hi @visheshvsh 

I understand you want to change the date format in the merge column in direct query mode. However, the FORMAT function is not supported in direct query mode.

There are some possible workarounds that you can try, depending on your data source and your desired output:

One option is to use the Power Query Editor to create a custom column that converts the date part of the merged column to text, and then concatenate it with the other parts. For example:

=
Text.From(Date.Day([MergedColumn]))&“/”&Text.From(Date.Month([MergedColumn]))&“/”&Text.From(Date.Year([MergedColumn]))&" "&[OtherParts]

This will create a new column with the date format as dd/mm/yyyy, followed by the other parts of the merged column. You can then use this column in your report. However, this option may not work if your data source does not allow transformations in direct query mode.

Another option is to use DAX to create a calculated column that extracts the date part of the merged column and formats it as text, and then concatenates it with the other parts. For example:

=
CONCATENATE(CONCATENATE(CONCATENATE(TEXT(DAY([MergedColumn]),“00”),“/”),CONCATENATE(TEXT(MONTH([MergedColumn]),“00”),“/”)),CONCATENATE(TEXT(YEAR([MergedColumn]),“0000”)," "&[OtherParts]))

This will create a new column with the date format as dd/mm/yyyy, followed by the other parts of the merged column. You can then use this column in your report. However, this option may not preserve the original data type of the date column, and may affect the sorting and filtering of the column.

If none of these options work for you, you may need to change the date format at the source level, or switch to import mode instead of direct query mode.

You can refer to the following link content:

Solved: Change date format in DIRECT Query Mode - not on l... - Microsoft Fabric Community

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.