Hello, I am getting data form a server that is connected to a machine that makes parts, every X time, and I need to be able to have a counter on real time that can show me how many pcs have been built and cycle time, I have been using direct query for similar reports with page refresh of 1 minute, but for this query ower Bi dont let me use direct query
let
Source = Sql.Databases("123"),
CompanyRecipeManagement01 = Source{[Name="456"]}[Data],
dbo_CustomerParts = CompanyRecipeManagement01{[Schema="dbo",Item="CustomerParts"]}[Data],
#"Added Custom" = Table.AddColumn(#"dbo_CustomerParts", "Machine", each "TR"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "BuildTime (min)", each Duration.TotalMinutes(([CompletedDate]-[StartDate]))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Shift", each if (Time.Hour([CompletedDate]) >= 6) and (Time.Hour([CompletedDate]) < 14) then "1st" else if (Time.Hour([CompletedDate]) >= 14) and (Time.Hour([CompletedDate]) < 22) then "2nd" else "3rd"),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "CompanyCompletedDate", each if (Time.Hour([CompletedDate]) >= 22) then
//after 5pm then want to report for next day
DateTime.Date(Date.AddDays([CompletedDate] ,1))
else
//report the same day
DateTime.Date([CompletedDate])),
#"Sorted Rows" = Table.Sort(#"Added Custom3",{{"StartDate", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"StartDate"}, {"Added Index1.StartDate"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Added Index1", "CycleTime (min)", each if Duration.TotalMinutes([Added Index1.StartDate]-[StartDate]) > 2*[#"BuildTime (min)"]
then
""
else
Duration.TotalMinutes([Added Index1.StartDate]-[StartDate])),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"UniqueIdentifier", "CompletionStatusId", "CompanyPN", "ChassisNumber", "SequenceNumber", "RackPosition", "RackID", "From", "To", "IsJobCompleted", "StartDate", "CompletedDate", "COMPANYCompletedDate", "CompletionStatus", "JobRecipeSteps", "Machine", "BuildTime (min)", "Shift", "Index", "Index.1", "Added Index1.StartDate", "CycleTime (min)"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"CycleTime (min)", type number}, {"Shift", type text}, {"CompletedDate", type date}, {"StartDate", type date}, {"BuildTime (min)", type number}, {"COMPANYCompletedDate", type date}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"CycleTime (min)"})
in
#"Removed Errors"
Can some one tell me how to getr a auto page refresh every minute without using Direct Query or how to transform the query to be able to use Direct Query.
