if else and while in M language

Question

There are 165 Values here and they are Comma Separated. This step is called CommaSeperated

workItemList is a function which takes in the value from CommaSeperated and Brings out the table here

I want to split the 165 items in CommaSeparated into batches of 100 and call workItemList for each batch. Any ideas on how it must be done?

Answer 1

i have managed to take a comma separated text string and turn it into a grouped table of 100's.

first 100 is called "100" next 100 is called "200" and starts at 101.

enter image description here

here are my steps enter image description here

First i split the text string into columns using comma as the separator tag, using the for each option.

Then i transposed the whole thing into one column. Add an index. Add a modulus of 100

Add a custom column "100counter": = Table.AddColumn(#"Inserted Modulo", "100counter", each if [Modulus]=99 then [Indeks]+1 else null)

forget the renamed columns step :D

used Fill up first, because the to initial 0-100 will have "null" as their modulus. used Fill down second, because the last records can be null if it doesnt end exactly on 99.

Grouped by "100counter", all rows.

Abbridged code - i cleaned up the 1700 columns in the split by delimiter step :

let
Source = Excel.CurrentWorkbook(){[Name="Tabel2"]}[Content],
#"Split Column by Delimiter" = //Alot of spam code here which is essentially just alll the columns being split. Mark ALL, choose split columns by delimiter, choose comma and all.
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Indeks", 0, 1),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulus", each Number.Mod([Indeks], 100), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Modulo", "100counter", each if [Modulus]=99 then [Indeks]+1 else null),
#"Filled Up" = Table.FillUp(#"Added Custom",{"100counter"}),
#"Filled Down" = Table.FillDown(#"Filled Up",{"100counter"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"100counter"}, {{"Antal", each _, type table [Column1=number, Indeks=number, Modulus=number, 100counter=number]}})

in #"Grouped Rows"