Power Query - Remove the enthier line if the count of rows is below 4

[Anonymous]

Hi,

I have a situation where I am mixing 1 week and 4 weeks data. I am adding the data in the 1 week to 4 weeks of data. All good! 

But sometimes the 1 week does not correspond with the 4 weeks. For instance, the latest data for the 4 weeks is week 24 (21, 22, 23, 24) and I have the 1 week until week 22. So I am missing week 23 and week 24.

I want to remove the latest data so that the data is in 4 weeks or it is not in the table. I want to remove the week 21 and the 22. and only have the data until week 20.

I have the following in the table:

How can I do that?

[Anonymous]

Hi All!,

I'll try to clarify! 🙂

In my Power Query, I have 2 tables. One with 4 weeks data:

This data comes with only one per Market/Produkt/Period. 

And then I receive the 1 weeks data like this

There is no rule of when I receive the different data. So sometimes the 1 weeks data is older than the 4 weeks and sometimes it is younger. But at this time it is younger...

What I need to do is to delete the data that is not a complete set (4 weeks / eg 21+22+23+24). The way I think this is done is by looking at have many weeks of data I have. Do I have eg. 4 weeks of data, everything is fine and I'll have the data, but do I have 3 weeks of data, I'll have to delete the 3 weeks of data.

I hope this clarifies! 🙂

Mb Kent

[Anonymous]

Hi @Anonymous ,

Sorry, actually I don't quite understand what you want. It's better to show me your expected results corresponding the table you have provided. 

For now, it is suggest to try to filter data as follows

Best Regards,

Stephen Tao

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

[Vera_33]

Hi @Anonymous 

So the context is always the latest data for the 4 weeks?  if the it is your source data

Table.SelectRows(Source, each if List.Max(Source[1 week]) <> List.Max(Source[4 week]) then [1 week] <= List.Max(Source[4 week])-4 else [1 week] <= List.Max(Source[4 week]))

[Anonymous]

Hi,

Please forgive me, but I'm fairly new to "M"! 

I have to write this into the "Data-table" and not to the "Calender-table"?

The reason that I ask is that I have a "Products" and "Market" in the "Data-table" and therefore I need to take this into consideration as well. To give the sum of 4 it has to calculate the sum of "Products" and "Market" and the "Period" (#Products + #Market + #Period = 4).

Also - I've read about the List.Max function and I don't think that I can use this. The reason for this is that my [1 week] is more frequent than my [4 weeks]. So sometimes this goes much further than my [4 weeks]. I get my [4 weeks] of data 4 times per year and my [1 week] 12 times...

So I think that I need to find another function.

[Vera_33]

Hi @Anonymous 

Agree with @Anonymous , you need to provide more sample data and expected result. I was asking whether it was always just the latest 4 weeks, so the actual data is not.