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 30. None of them have had all projects completed as you can see in the example. Here is the outcome I am looking for:
I want to see if there are any projects completed within 30 days of the Repair Visit and return the Project Name.
I also want the Within 30 days to indicate yes or no if a project is found.
I am super new to Power Query so any help would be great.
2 Replies
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 Custom1Thank 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:
