Find Aging given if an item has been sold or not in the past few Weeks

[anubhav247]

Hi BI Masters,

I have following data which shows if a particular item was sold in the past few week.

item	Sold	History Weeks
Apple	Yes	1
Apple	yes	2
Apple	Yes	3
Apple	Yes	4
Apple	Yes	5
Apple	Yes	6
Apple	No	7
Apple	Yes	8
Apple	Yes	9
Apple	Yes	10
Apple	Yes	11
Apple	Yes	12
Orange	Yes	1
Orange	yes	2
Orange	Yes	3
Orange	No	4
Orange	Yes	5
Orange	Yes	6
Orange	No	7
Orange	Yes	8
Orange	Yes	9
Orange	Yes	10
Orange	Yes	11
Orange	Yes	12

I need to transform this dat into this

item	Perfection Ageing
Apple	6
Orange	3

By Looking at this data, anyone can tell that apple is being bought for last 6 weeks continuously and Orange is being Bought for last 3 weeks Continuosly.

I need to do this transformation in Power Query and Not Dax because this data will be refeenced by many other transformations.

[v-yangliu-msft · Accepted solution]

Hi  @anubhav247 ,

Here are the steps you can follow：

1. Add Column – Conditional Column – Look at the yellow label.

2. Transform – Group BY.

3. Add Column – Custom Column – Enter the following code.

4. Select [Count] – Remove Columns.

5. Result:

Best Regards,

Liu Yang

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

[anubhav247]

***Adding info that might not be obvious***

Number "1" in History week column means last week, "2" means last to last week and so on