Complete Beginner Help - Need Power BI to pull 2 columns instead of 4

[ckozlowski]

Current state we have a "hot list" that tells us which work needs to be done. This hot list pulls from orders that have been placed and are awaiting approval and also orders that have been approved. I am trying to only pull the comluns that show orders which have already been approved and can not figure this one out. I have enlisted some help, but we keep running into duplication errors. 

It is a code that was written a few years back and is not the cleanest. 

= OleDb.DataSource("provider=SQLNCLI11.1;initial catalog=OEPDB;data source=DYNAMICS_AGL;application intent=READONLY", [Query="select #(lf)#(tab)rtrim(a.[Item Number]) [Kit],#(lf)#(tab)rtrim(a.[Item Description]) [Kit Description],#(lf)#(tab)rtrim(a.Mktg) Mktg,#(lf)#(tab)rtrim(a.[Item Classification]) [Item Classification],#(lf)#(lf)#(tab)isnull(d.[Awaiting Release],0) + isnull(x.[Awaiting Approval],0) + isnull(fillbatch.QtyPrinted,0) + isnull(fillbatch.QtyUnprinted,0) [Kits Scheduled to Ship],#(lf)#(tab)isnull(d.[Awaiting Release],0) [Awaiting Release] ,#(lf)#(tab)isnull(x.[Awaiting Approval],0) [Awaiting Approval],#(lf)#(tab)isnull(fillbatch.QtyPrinted,0) FillBatchPrinted,#(lf)#(tab)isnull(fillbatch.QtyUnprinted,0) FillBatchUnprinted,#(lf)#(tab)isnull(fillbatch.QtyPrinted,0) + isnull(fillbatch.QtyUnprinted,0) FillBatchTotal,#(lf)#(tab)isnull(KRAP.KRAPQTY,0) [Kits Avail in Kits Returned],#(lf)#(tab)isnull(paragon28.P28QTY,0) [Kits Avail in Paragon 28],#(lf)#(tab)isnull(KRAP.KRAPQTY,0) + isnull(paragon28.P28QTY,0) [Total Kits In House],#(lf)#(lf)#(tab)case when#(lf)#(tab)(case when #(lf)#(tab)#(tab)(isnull(d.[Awaiting Release],0) + isnull(x.[Awaiting Approval],0) + isnull(fillbatch.QtyPrinted,0) + isnull(fillbatch.QtyUnprinted,0)) > isnull((paragon28.P28QTY),0) #(lf)#(tab)#(tab)#(tab)then (isnull(d.[Awaiting Release],0) + isnull(x.[Awaiting Approval],0) + isnull(fillbatch.QtyPrinted,0) + isnull(fillbatch.QtyUnprinted,0) - isnull((paragon28.P28QTY),0))#(lf)#(tab)#(tab)#(tab)else 0 end)#(lf)#(tab)#(tab)#(tab)#(tab)> isnull(KRAP.KRAPQTY,0)#(lf)#(tab)#(tab)#(tab)#(tab)then isnull(KRAP.KRAPQTY,0)#(lf)#(tab)#(tab)#(tab)#(tab)else (case when #(lf)#(tab)#(tab)(isnull(d.[Awaiting Release],0) + isnull(x.[Awaiting Approval],0) + isnull(fillbatch.QtyPrinted,0) + isnull(fillbatch.QtyUnprinted,0)) > isnull((paragon28.P28QTY),0) #(lf)#(tab)#(tab)#(tab)then (isnull(d.[Awaiting Release],0) + isnull(x.[Awaiting Approval],0) + isnull(fillbatch.QtyPrinted,0) + isnull(fillbatch.QtyUnprinted,0) - isnull((paragon28.P28QTY),0))#(lf)#(tab)#(tab)#(tab)else 0 end)#(lf)#(tab)#(tab)#(tab)#(tab)end as [Kits Needed From KRAP]#(lf)#(lf), isnull(j.AvgEOD, 0) [Avg OH EOD]#(lf)#(lf), z.[Avg Daily Shipments] [Avg Daily Shipments]#(lf)#(lf), Ftran.DueTomorrow #(lf), ftran2.Overdue#(lf), Case when datename(dw, getdate()) = 'Friday'#(lf)#(tab)#(tab)then convert(date, (Getdate() + 3))#(lf)#(tab)#(tab) when datename(dw, getdate()) = 'Saturday'#(lf)#(tab)#(tab)#(tab)#(tab) then convert(date, (Getdate() + 2))#(lf)#(tab)else #(tab)convert(date, (Getdate() + 1))#(lf) end as Ftrandate#(lf)#(lf)from#(lf)(#(lf)--[Get BOM]#(lf)#(tab)select distinct #(lf)#(tab)rtrim(['Item Quantity Master'].[ITEMNMBR]) as 'Item Number', #(lf)#(tab)rtrim(['Item Master'].[ITEMDESC]) as 'Item Description',#(lf)#(tab)rtrim(['Item Master'].ITMSHNAM) as 'Mktg',#(lf)#(tab)rtrim(['Item Master'].itmclscd) as 'Item Classification'#(lf)#(tab)from p28..[IV00102] as ['Item Quantity Master'] with (NOLOCK) #(lf)#(tab)left outer join p28..[IV00101] as ['Item Master'] with (NOLOCK) on ['Item Quantity Master'].[ITEMNMBR] = ['Item Master'].[ITEMNMBR] #(lf)#(tab)where #(lf)#(tab)['Item Master'].inactive = 0 and ['Item Master'].itemtype <> 2#(lf)#(tab)and ['Item Master'].itmclscd in ('LOANER KIT','ALLOG-KIT')#(lf)) a#(lf)#(lf)---------------------------------Awaiting Release--------------------------------------------#(lf)left join(#(lf)select#(lf)#(tab)b.itemnmbr [Item Number]#(lf)#(tab), count(b.itemnmbr) [Awaiting Release]#(lf)#(lf)from oepdb.dbo.orderheader a#(lf)#(lf)left join oepdb.dbo.orderdetail b on a.OPOrderNumber = b.OPOrderNumber#(lf)left join oepdb.dbo.sysuser c on a.UserID = c.userid#(lf)left join oepdb.dbo.sysuser d on a.ApprovedBy = d.userid#(lf)#(lf)where a.OPOrderStatus = 'Approved'#(lf)#(tab)and (a.SHIPDATE = convert(date, GETDATE()) or Flight_Hold_Indicator = 1)#(lf)#(tab)and CaseType != 'A'#(lf)#(tab)and SurgeonName != 'Demo, Demo'#(lf)#(tab)and GP_SOPNUMBER is null#(lf)#(tab)and SHIPMTHD != 'Extension'#(lf)#(tab)and SHIPMTHD != 'Field Transfer'#(lf)#(lf)group by b.itemnmbr#(lf)) d on a.[Item Number] = d.[Item Number]#(lf)#(lf)---------------------------------Awaiting Approval----------------------------------------#(lf)left join(#(lf)select #(lf)#(tab)b.itemnmbr [Item Number]#(lf)#(tab), count(b.itemnmbr) [Awaiting Approval]#(lf)#(lf)from oepdb.dbo.orderheader a#(lf)#(lf)left join oepdb.dbo.orderdetail b on a.OPOrderNumber = b.OPOrderNumber#(lf)left join oepdb.dbo.sysuser c on a.UserID = c.userid#(lf)left join oepdb.dbo.sysuser d on a.ApprovedBy = d.userid#(lf)#(lf)where a.OPOrderStatus = 'Internal Review'#(lf)#(tab)and a.SHIPDATE = convert(date, GETDATE())#(lf)#(tab)and CaseType != 'A'#(lf)#(tab)and SurgeonName != 'Demo, Demo'#(lf)#(tab)and GP_SOPNUMBER is null#(lf)#(tab)and SHIPMTHD != 'Extension'#(lf)#(lf)Group by b.itemnmbr#(lf)) x on a.[Item Number] = x.[Item Number]#(lf)--------------------------------------------------------------------------------#(lf)#(lf)--------------------------------Avg Daily------------------------------#(lf)left join(#(lf)select #(lf)#(tab)y.itemnmbr#(lf)#(tab),count(itemnmbr)/(#(lf)select #(lf)count(distinct(#(lf)convert(date,SHIPDATE)#(lf)))#(lf)from oepdb.dbo.orderheader #(lf)where SHIPDATE > (getdate()-30) and shipdate < (getdate() -1)#(lf)and datename(dw, SHIPDATE) not in ('Saturday','Sunday')#(lf)) [Avg Daily Shipments]#(lf)from oepdb.dbo.orderheader x#(lf)inner join oepdb.dbo.orderdetail y on x.OPOrderNumber = y.OPOrderNumber#(lf)where SHIPMTHD not in ('EXTENSION','FIELD TRANSFER')#(lf)and SHIPDATE > (getdate() - 30) and shipdate < (getdate() -1)#(lf)and OPOrderStatus = 'Complete'#(lf)and x.OPOrderNumber like 'OP%'#(lf)group by itemnmbr#(lf)) z on a.[Item Number] = z.itemnmbr#(lf)#(lf)-----------------------------Avg EOD OH-------G---------------------------------#(lf)left join(#(lf)select #(lf) j.kit#(lf)#(tab) --, stdev(j.[Avg Daily Shipments])#(lf) , sum([Total Kits In House])/(#(lf)select #(lf)count(distinct(j.date#(lf)))#(lf)from p28.dbo.EOD_Kits_OH j#(lf)#(lf)where datename(dw, date) not in ('Saturday','Sunday')#(lf)and j.Date > dateadd(day, -30, getdate())#(lf)#(lf)) AvgEOD#(lf)from p28.dbo.EOD_Kits_OH j#(lf)where datename(dw, date) not in ('Saturday','Sunday')#(lf)and j.Date > dateadd(day, -30, getdate())#(lf)#(lf)group by j.kit#(lf)) j on a.[Item Number] = j.kit#(lf)-------------------------------------------------------------------------------------------#(lf)#(lf)--------------------------------------Due Tomorrow --------------Ftran----------------------#(lf)left join (#(lf)select #(lf) count(a.SOPNUMBE) DueTomorrow#(lf) , rtrim(a.ITEMNMBR) itemnmbr#(lf)from p28.dbo.SOP10201 a#(lf)left join p28.dbo.SOP10106 b on a.SOPNUMBE = b.SOPNUMBE#(lf)left join oepdb.dbo.orderdetail c on a.SOPNUMBE = c.GP_SOPNUMBER and a.ITEMNMBR = c.itemnmbr and a.SERLTNUM = c.SerialNumber#(lf)left join oepdb.dbo.orderheader d on c.OPOrderNumber = d.OPOrderNumber#(lf)where a.SOPTYPE = '2'#(lf)and convert(date, b.USRDAT02) = #(lf)(select #(lf) Case when datename(dw, getdate()) = 'Friday'#(lf) then convert(date, (Getdate() + 3))#(lf)#(tab)#(tab)#(tab) when datename(dw, getdate()) = 'Saturday'#(lf) then convert(date, (Getdate() + 2))#(lf) else convert(date, (Getdate() + 1))#(lf)end)#(lf)and CaseType = 'F'#(lf)group by a.ITEMNMBR#(lf)#(lf)#(lf)) ftran on a.[Item Number] = ftran.itemnmbr#(lf)--------------------------------------------------------------------------------------------#(lf)--------------------------------------Overdue --------------Ftran2----------------------#(lf)left join (#(lf)select#(lf)#(tab)count(a.SOPNUMBE) Overdue#(lf)#(tab), rtrim(a.ITEMNMBR) itemnmbr#(lf)from p28.dbo.SOP10201 a#(lf)left join p28.dbo.SOP10106 b on a.SOPNUMBE = b.SOPNUMBE#(lf)left join oepdb.dbo.orderdetail c on a.SOPNUMBE = c.GP_SOPNUMBER and a.ITEMNMBR = c.itemnmbr and a.SERLTNUM = c.SerialNumber#(lf)left join oepdb.dbo.orderheader d on c.OPOrderNumber = d.OPOrderNumber#(lf)where a.SOPTYPE = '2'#(lf)and convert(date, b.USRDAT02) <#(lf)(select #(lf)#(tab)Case when datename(dw, getdate()) = 'Friday'#(lf)#(tab)#(tab)then convert(date, (Getdate() + 3))#(lf)#(tab)else #(tab)convert(date, (Getdate() + 1))#(lf) end)#(lf)and CaseType = 'F'#(lf)group by a.ITEMNMBR#(lf)#(lf)) ftran2 on a.[Item Number] = ftran2.itemnmbr#(lf)-------------------------------------------------------------------------------------------------------#(lf)#(lf)-------------------------KRAP----------------------------------#(lf)left join (#(lf)#(tab)select distinct#(lf)#(tab)#(tab)KRAP.itemnmbr#(lf)#(tab)#(tab), count(KRAP.SERLNMBR) KRAPQTY#(lf)#(lf)#(tab)from p28.dbo.IV00200 KRAP#(lf)#(tab)where KRAP.LOCNCODE = 'Kits Ret'#(lf)#(tab)and KRAP.SERLNSLD = '0'#(lf)#(tab)group by ITEMNMBR#(lf)) KRAP on a.[Item Number] = KRAP.ITEMNMBR#(lf)------------------------------------------------------------------#(lf)#(lf)-------------------------Paragon28----------------------------------#(lf)left join (#(lf)#(tab)select distinct#(lf)#(tab)#(tab)Paragon28.itemnmbr#(lf)#(tab)#(tab), count(Paragon28.SERLNMBR) P28QTY#(lf)#(lf)#(tab)from p28.dbo.IV00200 Paragon28#(lf)#(tab)where Paragon28.LOCNCODE = 'Paragon 28'#(lf)#(tab)and Paragon28.SERLNSLD = '0'#(lf)#(tab)group by ITEMNMBR#(lf)) Paragon28 on a.[Item Number] = Paragon28.ITEMNMBR#(lf)------------------------------------------------------------------#(lf)-------------------------FillBatch----------------------------------#(lf)left join (#(lf)select#(lf)#(tab)a.itemnmbr#(lf)#(tab), isnull((select#(lf)#(tab)#(tab)#(tab)count(a.itemnmbr)#(lf)#(tab)#(tab)where TIMESPRT > 0),0) QtyPrinted#(lf)#(tab), isnull((select#(lf)#(tab)#(tab)#(tab)count(a.itemnmbr)#(lf)#(tab)#(tab)where TIMESPRT = 0),0) QtyUnprinted#(lf)from p28.dbo.SOP10200 a#(lf)left join p28.dbo.SOP10100 b on a.SOPNUMBE = b.SOPNUMBE#(lf)where QUANTITY != QTYFULFI#(lf)and a.SOPTYPE = '2'#(lf)and b.BACHNUMB like 'FILL%'#(lf)group by ITEMNMBR, TIMESPRT#(lf)#(lf)) FillBatch on a.[Item Number] = FillBatch.ITEMNMBR#(lf)------------------------------------------------------------------#(lf)#(lf)#(lf)order by [Avg Daily Shipments] desc"])

The goal is to remove "Awaiting Release" and "Awaiting Approval" while keeping "FillBatchUnprinted" and "FillBatchPrinted"

Please, any help would be amazing. 

[BA_Pete]

Hi @ckozlowski ,

Firstly, I don't think I can solve this issue for you as the core of it appears to be an SQL issue. However, here's a couple of things you can do to help you get this cleared up:

1) The error you're getting when passing the query to the data model is quite descriptive in the issue: 'Kit Hot List' query is generating duplicated values within a dimension table.

Partial fix: On the Model view, either disable any relationships where 'Kit Hot List'[ALLECT] is on the ONE side, or change the cardinality of these relationships so that 'Kit Hot List'[ALLECT] is on the MANY side of each. This should allow your query to load to the data model, but will not fix that fact that duplicates are being created in the first place.

2) Creating a robust/functioning query per your requirements is an SQL code issue. Whilst there are plenty of SQL ninjas on the Power Query board, it's not really the ideal place to get detailed help on it such as you need.

Partial fix: Take some time to properly format the SQL query i.e. everywhere there is a '#(lf)' swap it for an actual Line Feed, anywhere there is a #(tab) swap it for an actual Tab etc. Once you have the properly formatted SQL, post it on an SQL-specific forum with your editing requirements and someone there will probably be able to amend it to your needs pretty quickly.

Pete

[Anonymous]

I can't process what it is I'm seeing, nor what you are asking. But you need to UPPERCASE that dimension column and THEN remove duplicates.

--Nate