DirectQuery Transpose Column Data to Multiple Rows via SQL

[ma_azj24]

Hi, I'm working with a dataset from SAP HANA and am storing the database via DirectQuery. I have been transforming the table output via SQL but need help with returning values from the "Scheduled" column based on the "Category" as new columns.

From

Date	Vehicle Code	From Loc	To Loc	Category	Scheduled
21/05/2024	Q123	SYD	BNE	Depart	21/05/2024 10:00
21/05/2024	Q123	SYD	BNE	Arrive	21/05/2024 11:00
21/05/2024	Q123	OOL	BNE	Depart	21/05/2024 09:00
21/05/2024	Q123	OOL	BNE	Arrive	21/05/2024 10:00

To

Date	Vehicle Code	From Loc	To Loc	Depart	Arrive
21/05/2024	Q123	SYD	BNE	21/05/2024 10:00	21/05/2024 11:00
21/05/2024	Q123	OOL	BNE	21/05/2024 09:00	21/05/2024 10:00

[amitchandak]

@ma_azj24 , If you want to do that in Power Query (Unpivot column) and this table will become import mode table

of you can create you use measures

calculate(  Min(Table[Scheduled]), filter(Table, Table[Category] = "Depart" ) )

calculate(  Min(Table[Scheduled]), filter(Table, Table[Category] = "Arrive" ) )

[ma_azj24]

Thank you for suggesting a solution. Unfortunately, I am required to use DirectQuery so using Power Query to transform the data is not an option for me. I will keep this in mind for my import mode datasets! 🙂