Forum Discussion
Justair07
Resolver I
Resolver IPower BI Returning ZERO when SSMS and Excel Both Return Values
Hi,
I'm completely stupped on this issue. As you can see from the screenshot below, the field IncrementalQtyGood in Power BI table is showing 0's for all OrderNum's that start with "LEG". It also shows 0's in the query in Power BI. In SSMS you can see that there are values and in Excel you can see there are values. I also attached screenshot of the datatype for the field in question.
Any help is much appreciated, I'm kinda freaking out about this because it makes me wonder where else data could be missing.
I'm a silly boy, as usual, I didn't look at my SQL Statement in Query Editor where it is clear as day that I'm filtering on TransactionName. That was filtering out all of my values for those OrderNums. :manindifferent:
let Source = Sql.Databases("augshopvue"), SV_TEST = Source{[Name="SV_TEST"]}[Data], dbo_vShopActivityForReports = SV_TEST{[Schema="dbo",Item="vShopActivityForReports"]}[Data], #"Removed Columns" = Table.RemoveColumns(dbo_vShopActivityForReports,{"TransactionNum", "TransactionClassification"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([TransactionName] = "ReportQty")), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"EmployeeID", "OrderStepOrIndir", "ActualFinishTime", "AdjustedFinishTime", "IsSupervisorInsert", "HasBeenEdited", "IsSetupComplete", "IsOperationComplete", "LaborHours", "MachineHours", "WorkpointFullName", "OrderStepSequenceNum", "ItemNum", "IndirectLaborNum", "IndirectDescription", "ShiftSetAssigned", "SetupLaborHours", "RunLaborHours", "ThisOperatorLaborHours", "ContributingTeamHours", "ThisTeamMemberLaborHours", "ThisTeamMemberSlushHours", "ThisTeamMemberTeamHours", "IndirectHours", "SetupMachineHours", "RunMachineHours", "LaborEfficiency", "MachineEfficiency", "EarnedHours", "SetupType", "DowntimeReason", "PauseReason", "LookUpNum1", "LookUpNum2", "JobClassNum", "IsOpenInterval", "IsMachineTran", "WasStartTimeAdjusted", "WasFinishTimeAdjusted", "RevisedByWhom", "RevisionDateTime", "IsBatchExport1Done", "IsBatchExport2Done", "IsRealTimeExportDone", "RealTimeErrorMessage", "Batch1ExportDateTime", "Batch2ExportDateTime", "RealTimeExportDateTime", "Comment", "StandardPiecesPerHour", "StandardSetupHours", "Note", "GroupByNum", "StandardMachineHours", "ReworkParentOrderNum", "ReworkParentOrderStepSequenceNum", "ReworkLikeOrderStepSequenceNum", "ReworkIsFinalStep", "IsReworkOrder", "SerialNum", "LotNum", "MaterialNum", "SubstitutedForMaterialNum", "QtyIssued", "LocationID", "BinID", "InternalNum1", "InternalNum2", "InternalNum3", "InternalNum4", "InternalNum5", "Attrib1", "Attrib2", "Attrib3", "Attrib4", "Attrib5", "Attrib6", "Attrib7", "Attrib8", "Attrib9", "Attrib10", "Attrib11", "Attrib12", "Attrib13", "Attrib14", "Attrib15", "FirstName", "LastName", "WorkpointDescription", "QtyGood", "QtyScrap", "IsRework"}), #"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([DepartmentNum] = "761" or [DepartmentNum] = "762" or [DepartmentNum] = "765")) in #"Filtered Rows2"
1 Reply
Justair07I'm a silly boy, as usual, I didn't look at my SQL Statement in Query Editor where it is clear as day that I'm filtering on TransactionName. That was filtering out all of my values for those OrderNums. :manindifferent:
let Source = Sql.Databases("augshopvue"), SV_TEST = Source{[Name="SV_TEST"]}[Data], dbo_vShopActivityForReports = SV_TEST{[Schema="dbo",Item="vShopActivityForReports"]}[Data], #"Removed Columns" = Table.RemoveColumns(dbo_vShopActivityForReports,{"TransactionNum", "TransactionClassification"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([TransactionName] = "ReportQty")), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"EmployeeID", "OrderStepOrIndir", "ActualFinishTime", "AdjustedFinishTime", "IsSupervisorInsert", "HasBeenEdited", "IsSetupComplete", "IsOperationComplete", "LaborHours", "MachineHours", "WorkpointFullName", "OrderStepSequenceNum", "ItemNum", "IndirectLaborNum", "IndirectDescription", "ShiftSetAssigned", "SetupLaborHours", "RunLaborHours", "ThisOperatorLaborHours", "ContributingTeamHours", "ThisTeamMemberLaborHours", "ThisTeamMemberSlushHours", "ThisTeamMemberTeamHours", "IndirectHours", "SetupMachineHours", "RunMachineHours", "LaborEfficiency", "MachineEfficiency", "EarnedHours", "SetupType", "DowntimeReason", "PauseReason", "LookUpNum1", "LookUpNum2", "JobClassNum", "IsOpenInterval", "IsMachineTran", "WasStartTimeAdjusted", "WasFinishTimeAdjusted", "RevisedByWhom", "RevisionDateTime", "IsBatchExport1Done", "IsBatchExport2Done", "IsRealTimeExportDone", "RealTimeErrorMessage", "Batch1ExportDateTime", "Batch2ExportDateTime", "RealTimeExportDateTime", "Comment", "StandardPiecesPerHour", "StandardSetupHours", "Note", "GroupByNum", "StandardMachineHours", "ReworkParentOrderNum", "ReworkParentOrderStepSequenceNum", "ReworkLikeOrderStepSequenceNum", "ReworkIsFinalStep", "IsReworkOrder", "SerialNum", "LotNum", "MaterialNum", "SubstitutedForMaterialNum", "QtyIssued", "LocationID", "BinID", "InternalNum1", "InternalNum2", "InternalNum3", "InternalNum4", "InternalNum5", "Attrib1", "Attrib2", "Attrib3", "Attrib4", "Attrib5", "Attrib6", "Attrib7", "Attrib8", "Attrib9", "Attrib10", "Attrib11", "Attrib12", "Attrib13", "Attrib14", "Attrib15", "FirstName", "LastName", "WorkpointDescription", "QtyGood", "QtyScrap", "IsRework"}), #"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([DepartmentNum] = "761" or [DepartmentNum] = "762" or [DepartmentNum] = "765")) in #"Filtered Rows2"