Loop id's through each week and compare with next week and find only unique ids

[Anonymous]

I have a table with week number, name, amount, category in Power bi.

I want to create a table. The table should have only the names that are unique(for a particular id- different category/amount when compared with next week) in the consecutive weeks.

Eg: calculate unique names in rows 29,30. Now 30,31 and so on. If the name is duplicate in 29,30 and is present unique in 30,31. I want that data.

Please help me out. I am struggling with for a week now

If you have records for multiple weeks:

Record Id Forecast Category Amount WeekNumber

1	Category A	100	30
1	Category A	100	31
2	Category B	200	30
2	Category C	300	31
3	Category D	400	30
1	CaetgoryB	100	32

Output:

2	Category B	200	30
2	Category C	300	31
3	Category D	400	30
1	CaetgoryB	100	32

[Anonymous · Accepted solution]

Hi @Anonymous 

Thanks for the reply from SamWiseOwl.

@Anonymous , I added a row to the sample data you gave for your reference.

Create a calculated column as follows

IsConsecutive = 
VAR _CurrentRowWeekNumber = [WeekNumber]
VAR _NextRowWeekNumber = 
    CALCULATE(
        SELECTEDVALUE('Table'[WeekNumber]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Forecast Category]), 
            'Table'[WeekNumber] > _CurrentRowWeekNumber 
        )
    )
VAR _LastRowWeekNumber = 
CALCULATE(
        SELECTEDVALUE('Table'[WeekNumber]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Forecast Category]), 
            'Table'[WeekNumber] < _CurrentRowWeekNumber 
        )
    )
VAR _IsConsecutiveWeek = IF(_NextRowWeekNumber = _CurrentRowWeekNumber + 1 || _LastRowWeekNumber = _CurrentRowWeekNumber - 1, FALSE(), TRUE())
RETURN
_IsConsecutiveWeek

Create a calculated table as follows

OutputTable = 
SELECTCOLUMNS (
    FILTER ( 'Table', 'Table'[IsConsecutive] = TRUE () ),
    "Record Id", [Record Id],
    "Category", [Forecast Category],
    "Amount", [Amount],
    "WeekNumber", [WeekNumber]
)

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I am using a looping logic in power query. But, its taking a long time to get refreshed. So, I wanted a efficient solution in the form of calculated table/column.

The power query for looping is below,
let
CompareWeeks = (CurrentWeek as number) as table =>
let
// Get the current week data
CurrentWeekData = Table.SelectRows(#"Exact Snapshot", each [weeknumber] = CurrentWeek),

// Get the next week data
NextWeekData = Table.SelectRows(#"Exact Snapshot", each [weeknumber] = CurrentWeek + 1),

// Combine current and next week data
CombinedData = Table.Combine({CurrentWeekData, NextWeekData}),

// Find duplicates based on multiple columns
GroupedData = Table.Group(CombinedData, {"Record Id", "Forecast Category", "Amount"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

// Find IDs that appear in both weeks
DuplicateRows = Table.SelectRows(GroupedData, each [Count] = 2),

// Extract the duplicate rows
DuplicateCombinations = Table.SelectColumns(DuplicateRows, {"Record Id", "Forecast Category", "Amount"}),

// Filter out duplicates from combined data
UniqueData = Table.SelectRows(CombinedData, each not List.Contains(
Table.ToRows(DuplicateCombinations),
{ [Record Id], [Forecast Category], [Amount] }
))

in
UniqueData
in
CompareWeeks

[Anonymous]

Thanks for the reply. But I can see still the data is not correctly filtered. In my data, the weeks are from 29-33. The calculation has to be done for consecutive weeks. Eg: Find unique records in 29,30-save it. Then 30,31 - save it. 31,32 - save it. 32-33 - save it. This is kind of overwriting it for all weeks. I can see Y only on week29 and 33 now. But there are unique values when only 2 weeks are compared

[SamWiseOwl]

Hi @Anonymous ahh I was trying to be sneaky and jumped the bucket.

Now it will only compare to the week previous. So compare 30 to 29, 31 to 30 etc

Y or no =

var week = [Week Number] --store week

var code = [Record Id Forecast] & [Category] & [Amount] --Create unique combo

var filterlist =

FILTER(

    'Forecast table' --filter the table to 1 week before and after with same code

    ,[Week Number] >= week-1 && [Week Number] <= week  && [Record Id Forecast] & [Category] & [Amount] = code)

return    

if(COUNTROWS(filterlist) > 1, "N", "Y") --if more than 1 row return N to use as a filter

[SamWiseOwl]

Hi @Anonymous 

This is not elegant but will do the job as a calculated column:

Y or no =

var week = [Week Number] --store week

var code = [Record Id Forecast] & [Category] & [Amount] --Create unique combo

var filterlist =

FILTER(

    'Forecast table' --filter the table to 1 week before and after with same code

    ,[Week Number] >= week-1 && [Week Number] <= week + 1 && [Record Id Forecast] & [Category] & [Amount] = code)

return    

if(COUNTROWS(filterlist) > 1, "N", "Y") --if more than 1 row return N to use as a filter

[Anonymous]

I can see that the weeks work fine. But, if its duplicate in consecutive weeks, I want to remove both rows containing the same id. 

In this query, I can see we are keeping 1 row even if its duplicate.

Eg: 

1	Category A	100	30	N
1	Category A	100	31	N

[SamWiseOwl]

@Anonymous  is this it?

Y or no =

var week = [Week Number]

var code = [Record Id Forecast] & [Category] & [Amount]

var filterlist =

FILTER(

    'Forecast table'

    ,[Week Number] >= week-1 && [Week Number] <= week && [Record Id Forecast] & [Category] & [Amount] = code)

var final = if(COUNTROWS(filterlist) > 1, "N", "Y")

return    

final

[Anonymous]

No. I want "N" at both the rows that are duplicated. Right now, I can see Y in one row. 

1	Category A	100	30	Y
1	Category A	100	31	N

[SamWiseOwl]

Right I'm lost.
This is what you asked for:

Output:

2	Category B	200	30
2	Category C	300	31
3	Category D	400	30
1	CaetgoryB	100	32

This was what I gave:

This removed those two rows, what was wrong with it in the first place?

[Anonymous]

But, when I applied in my data, I can see one row as "Y"

1	Category A	100	30	Y
1	Category A	100	31	N

But, I want "N" in both rows

[SamWiseOwl]

@Anonymous 

Y or no =

var week = [Week Number]

var code = [Record Id Forecast] & [Category] & [Amount]

var filterweekbefore =

FILTER(

    'Forecast table'

    ,[Week Number] >= week-1 && [Week Number] <= week && [Record Id Forecast] & [Category] & [Amount] = code)

var filterweekafter =

FILTER(

    'Forecast table'

    ,[Week Number] >= week && [Week Number] <= week + 1 && [Record Id Forecast] & [Category] & [Amount] = code)

var final = if(COUNTROWS(filterweekbefore) > 1 ||COUNTROWS(filterweekafter) > 1, "N", "Y")

return    

final

This one independently checks for row 30 if either 29 Or 31 matches.

If either matches you get N otherwise Y

[SamWiseOwl]

Let me know if this one works. I think it is what you are looking for. @Anonymous 

[Anonymous]

Eg: I have weeks from 29-32. First compare 29,30 - So, 1 is unique. List as 'Y'. Compare 30,31 - duplicate - 'N'.

1	Category A	100	30	Y
1	Category A	100	31	N
1	Category A	100	32	N