Forum Discussion
Create Groups of Equal Size
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 stayThe 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.
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 ResultI 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
6 Replies
- SundarRaj
Super User
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, 
sanalyticsANBILY1
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 ResultI 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
v-pnaroju-msftCommunity Support
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.
- 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, 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. - 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.
