Create Groups of Equal Size

[ANBILY1]

Dear Sir, as per the example below, I need to group by 5 blank spaces in the value column in relation to the date and code column, that 
is, 5 consecutive dates from the most recent onwards that have the same code.

This is how it is now

This is how I need it to stay

The dates column must contain the list with the 5 dates (I will expand it later), if there is no code with five dates, for example if I have 12 lines I would have two groups of 5 and 1 with 2 then I filter and leave only those that have 5. The important thing is to separate as many as possible by group of 5.

Thank you in advance for your attention.

[v-pnaroju-msft]

Hi ANBILY1,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @Omid_Motamedise, for your response.

Hi ANBILY1,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[Omid_Motamedise]

Just easily use Table.Split inside the Table.Group as follow

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1SGOdCKadIaRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Code"}, {{"Count", each List.Transform(Table.Split(_,5),Table.RowCount)}}),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Count")
in
    #"Expanded Count"

[v-pnaroju-msft]

Thankyou @SundarRaj and @sanalytics for your response.

Hi ANBILY1,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @SundarRaj  and @sanalytics  to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[sanalytics]

@ANBILY1 
Below is the correct code which dynamically group the code and based on that it splits the table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdExDoAgEAXRu2yNCaAolC57C8L9ryEdGKe3oHkhcT62Jj5swW/RxyRO7nGku6ERdUc9UBPqiXqhZtRCOojurtsUtyluU9ymuE1/+tr6khVfsmKZYZlhmWGZYZlhmWHZ1IxaSF//eCpuC99t/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, Value = _t]),
    TypeChanged = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Code", type text}, {"Value", type text}}),
    Result = Table.Combine( 
let
CodeGrouping =
Table.Group(
TypeChanged,{"Code"},{"AllRows", each _ }
)[AllRows],
SplitTable =
List.Combine(
List.Transform( 
CodeGrouping,(x) =>
Table.Split(x,5)
) ),
Result =
List.Transform( 
SplitTable,(x) =>
let
RowCountAdded =Table.AddColumn(
    x,"RowCount",each Table.RowCount(x) ),
ListOfDatesAdded = 
Table.AddColumn(RowCountAdded,"Dates",each RowCountAdded[Date]),
DateRemoved = 
Table.SelectColumns(ListOfDatesAdded,
List.Select(Table.ColumnNames(ListOfDatesAdded), each _ <>"Date") ),
RemoveDuplicate = 
Table.Distinct(DateRemoved)
in 
RemoveDuplicate
)
in 
Result
)
in
    Result

I have imported more 12 rows as Code D. It dynamically groups and split the table as 5 rows. Below screenshot

one suggesation, please do not use List.FirstN if you are dealing with large data.. It technically slow  down the query performance.

Attaching the file for your reference.

Hope it helps

Regards,

sanalytics

[SundarRaj]

Hi @ANBILY1 ,
You need not group by the blank spaces, you can just the split the table in groups of 5 by using Table.Split. See the code below:
let
Source = YourTable
#"ListNos." = Number.RoundDown ( Table.RowCount ( Source ) / 5 ),
AllTable = Table.Split ( Source , 5 ),
FilteredOut4 = List.FirstN ( AllTable , #"ListNos."),
Custom1 = FilteredOut4
in
Custom1

I haven't added the rest of the columnsw which can be added as well. Let me know if you would need that as well.

Hi, coming back, I am attaching the file with all the transformations.

Thanks,