Forum Discussion

PowerRobots99's avatar
PowerRobots99
Helper II
2 years ago
Solved

PowerQuery - Checking if values are present in previous report date

Hello Everyone,

 

Please refer .PBIX file - File

 

In power query, we have to find out if WO is present in previous reporting date, if present then it should give True else False

Is there any way to achive this ?

 

Input - 

 

 

Output - 

 

Report DateReport WeekWO#Output_IsInPreviousReportDate?
28-04-2024181 
28-04-2024182 
28-04-2024183 
28-04-2024184 
28-04-2024185 
28-04-2024186 
28-04-2024187 
28-04-2024188 
28-04-2024189 
28-04-20241810 
06-05-2024191TRUE
06-05-2024192TRUE
06-05-2024193TRUE
06-05-20241912FALSE
06-05-20241917FALSE
06-05-2024196TRUE
06-05-20241989FALSE
06-05-2024198TRUE
06-05-20241915FALSE
06-05-20241918FALSE
Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    2 years ago

    Hi PowerRobots99 

     

    Do the following steps:

    • Do a group by Report Date - All rows
    • Add an index column starting in 1
    • Expand all columns except for date
    • Add a new column with the following code
    if  [Index] = 1 then null else if Table.RowCount(

Table.SelectRows(
Table.SelectRows(#"Expanded Count",(x) => x[Index] = [Index]-1) , (y) => y[#"WO#"] = [#"WO#"])) = 1 then true else false

    File attach.

     

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi PowerRobots99 

     

    Do the following steps:

    • Do a group by Report Date - All rows
    • Add an index column starting in 1
    • Expand all columns except for date
    • Add a new column with the following code
    if  [Index] = 1 then null else if Table.RowCount(

Table.SelectRows(
Table.SelectRows(#"Expanded Count",(x) => x[Index] = [Index]-1) , (y) => y[#"WO#"] = [#"WO#"])) = 1 then true else false

    File attach.