In this video we will transform status log data using m query, DAX and finally SQL. We will try several approaches: Query Editor and Dax, Query Editor and Merges, Native SQL, SQL View using SSMS and both import and direct query.
SQL Script:
with ordered_table as ( select TOP 1000 [ProductID], [Name], [ProductModel], [CultureID], [Date_Status_Update], [Status], [Description] FROM [AdventureWorks2019].[dbo].[Adventure_Works_Product_Description_Status_Log] Order by [ProductID] DESC, [CultureID] DESC ), grouped_table as( select [ProductID], [Name], [ProductModel], [CultureID], [Date_Status_Update], [Status], [Description], count([ProductID]) over( Order by [ProductID] DESC ) as _grp FROM ordered_table ), filled_table as( select [ProductID], _grp, first_value([Name]) over ( partition by _grp order by [ProductID] ) as [Name], first_value([ProductModel]) over ( partition by _grp order by [ProductID] ) as [ProductModel], first_value([CultureID]) over ( partition by _grp order by [ProductID] ) as [CultureID], [Date_Status_Update], [Status], first_value([Description]) over ( partition by _grp order by [ProductID] ) as [Description] from grouped_table ), ranked_table as( select [ProductID], [Name], [ProductModel], [CultureID], [Date_Status_Update], [Status], [Description], RANK() OVER ( PARTITION BY ProductID order by [Date_Status_Update] DESC ) 'dscrank' FROM filled_table ), final_table as ( select [ProductID], [Name], [ProductModel], [CultureID], [Date_Status_Update], [Status], [Description], dscrank from ranked_table where dscrank = 1 and [Status] is not null ) select [ProductID], [Name], [ProductModel], [CultureID], [Date_Status_Update], [Status], [Description] from final_table
