PQ function to filter first 4 rows of each Date

[G-san]

Hi everyone!

I'm new in PowerBI, so maybe this could be a nonsense question. I'm building a new dashboard using a database with repeated values on the same reported day. I aim to filter the 4 first rows each time the variable DATE changes. There is any way to do this? I tried so many times but I failed. 

This is an example of the data, and FILTER is 1 to identify the desired output.

VAR1	DATE	Value	Filter
Apple	01-01-2001	1	1
Grape	01-01-2001	2	1
Wine	01-01-2001	1	1
Banana	01-01-2001	1	1
Apple	01-01-2001	10	0
Grape	01-01-2001	15	0
Wine	01-01-2001	10	0
Banana	01-01-2001	18	0
Apple	02-01-2001	5	1
Grape	02-01-2001	5	1
Wine	02-01-2001	6	1
Banana	02-01-2001	4	1
Apple	18-01-2001	2	1
Grape	18-01-2001	2	1
Wine	18-01-2001	2	1
Banana	18-01-2001	2	1
Apple	18-01-2001	84	0
Grape	18-01-2001	84	0
Wine	18-01-2001	86	0
Banana	18-01-2001	88	0

Thanks in advance

[AlienSx]

Hello, @G-san 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZCxCoAgEIbfxdlAJcW1lh6hQRwcGoIQ6f2HjOoy7i7U4fzu+Lg/BDGUsi1CCqW7eo1SuhbXizKIaU8FYQN4XvPP8JhyPTyn3er84eXaAift7zij99AAftNwi3en8WNvqSN2b3mPdteeDPZxM/h2MxTcDCfdvke5c5yye4dz/zbcuccD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VAR1 = _t, DATE = _t, Value = _t, Filter = _t]),
    groups = Table.Group(Source, {"DATE"}, {{"all", each Table.FirstN(_, 4)}})
in
    Table.Combine(groups[all])

[edhans]

Try this. I generated the "New Filter" column.

Here is my code. My code removes the unnecessary columns and formats the New FIlter to be an integer, but that isn't shown in the above screencap. Wanted you to see the intermediate steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBNC8MgDIb/S84dqLTFq2Mf9zG6g/TgoYeCFCnb/5+yNnMkGSoBn4SHvN7D4G4aGji5+zmXIcTXlOtljs9phbHx4FKK5UvpQ75GqdL+eQVf15AINogf8/Jn+BiWfGTOu1X5keW6Q87av+OC3mID+k3FO7o7j3d7TXtm95q3ZHdt2WB3t4A3t0DRLXDWbVuSu8Q5u+1p7r8NW+7jGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"DATE", type date}}, "en-BM"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Value", Int64.Type}, {"Filter", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"DATE"}, 
            {
                {
                    "All Rows", 
                    each Table.AddIndexColumn(_, "Index", 1, 1), 
                        type table [VAR1=nullable text, DATE=nullable date, Value=nullable number, Filter=nullable number, Index = Int64.Type]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"VAR1", "Value", "Filter", "Index"}, {"VAR1", "Value", "Filter", "Index"}),
    #"Added New Filter" = Table.AddColumn(#"Expanded All Rows", "New Filter", each if [Index] <= 4 then 1 else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added New Filter",{{"New Filter", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"DATE", "VAR1", "Value", "New Filter"})
in
    #"Removed Other Columns"

The first thing I did is grouped by Date and stuck all other columns in ALL ROWS.

Then I edited manually the Group By step to add an Index Column in the grouping, so once grouped you see this:

Then I expanded the All Rows step, excluding the DATE field as that was already there. Finally I added a New Filter column that looked at the index. If it it was <=4, it returns 1, otherwise 0.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.