Forum Discussion
Help modifying a query
Thanks for responding, I am pulling from SQL (I think). I am not very familiar with DAX. When I look at your YTD samples, where would I enter the actual date range, and Would I repeat this command three times? Sorry for my inexperience, and thank you for you patience!
Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Here is the query:
Source = Sql.Database("Server", "Operations", [Query="SELECT TOP (100) PERCENT RTRIM(r.Name) AS rName, pr.Type, pr.Sequence, d.ParkRideID, pr.RideName, DATEDIFF(minute, d.TimeDown, d.TimeUp) AS DownTimeMins, d.TimeDown, d.TimeUp, s.Name, RTRIM(d.Reason) AS Reason, #(lf) RTRIM(d.Resolution) AS Resolution, RTRIM(d.Comments) AS Comments, pr.ParkID, p.Name AS Expr1#(lf)FROM dbo.ParkRideDowntimes AS d INNER JOIN#(lf) dbo.ParkRides AS pr ON pr.ID = d.ParkRideID INNER JOIN#(lf) dbo.Parks AS p ON pr.ParkID = p.ID INNER JOIN#(lf) dbo.DowntimeSubtypes AS s ON d.DowntimeSubtypeID = s.ID INNER JOIN#(lf) dbo.Rides AS r ON pr.RideID = r.ID#(lf)WHERE (d.timedown between '2019-01-01' and '2021-4-4')"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Expr1", "Site"}, {"ParkID", "Site #"}, {"Name", "Subtype"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "TimeDown", "TimeDown - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"TimeDown - Copy", "Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Year", type date}})
in
#"Changed Type"