Forum Discussion
Power Query find date within 30 days
Hello all! I have two tables in Power Query. One is for project completions and the other is a listing of repair work at the site. The project table shown here is only the first 4 projects out of...
let
Left = Excel.CurrentWorkbook(){[Name="Left"]}[Content],
Right = Excel.CurrentWorkbook(){[Name="Right"]}[Content],
Custom1 = let Unpivot_Left=Table.Buffer(Table.UnpivotOtherColumns(Left,{"Site Number"},"Proj","Date")) in #table(Table.ColumnNames(Right)&{"With 30 days?","Project Number"},Table.ToList(Right,each let a=List.Distinct(Table.SelectRows(Unpivot_Left,(x)=>x[Site Number]=_{0} and x[Date]>=_{1} and x[Date]<=Date.AddDays(_{1},30))[Proj]),b=if List.Count(a)>0 then {"Yes",Text.Combine(a,"/")} else {"No",null} in _&b))
in
Custom1
Thank you for the assistance. I have some questions. Why does the repair table duplicate the site number and how do I get it to see if the repair date is within 30 days after the project instead of before it? Also when I change the repair dates this is what happens: