How to combine List in power query into text (1000 at a time)

awoods8005

New member
Joined
Mar 19, 2020
Messages
4
Reaction score
0
Points
0
Excel Version(s)
Office 365 Excel
I am looking to convert a List in Power Query into a text string but divide it up by every 1000 items.
The List is a list of ID numbers that I am sending back to Oracle database, which has a 1000 item limit in the WHERE clause. Creating a temp table in Oracle is not an option.


So far, this is what I have.


let
Source = IDLIST,
IDLISTV2= "'"&Text.Combine(List.Distinct(Source[ID]),"','")&"'"
in
IDLISTV2


This returns all of the values of the list in one big text string, which is helpful, but wont work for what I am doing.


Assuming I had 3046 ID numbers, it would return
'ID1', 'ID2', ....., 'ID3045', 'ID3046'


I am looking for it to return
'ID1', 'ID2', .....'ID999', 'ID1000'
AND 'ID1001', 'ID1002',.....
...
...
AND ..... 'ID3045', 'ID3046'


It is essentially the same, but divided by every 1000. There needs to be a word or divider between every 1000 for Oracle to read it properly.
I thought about using some sort of loop command or maybe a mod function but dont have enough M or Power Query knowledge to know if they are viable options.


I have only been using M/Power Query in Excel for a couple of weeks now. I am very new to it and welcome any and all feedback.
If anyone knows anything that could help with this please let me know.
Thanks!
 
I used two M-functions: you have to exchange the red values as you need
getSingleString: 10 --> 1000
GetString: 100 --> ?

Code:
// getSingleString(i) =>
let
  sID = "'ID" & Text.From(i) & "'",
  result =  if Number.Mod(i,[COLOR=#ff0000]10[/COLOR]) = 0 then "AND " & sID else sID
in
  result


// GetString
let
    Source = List.Generate(
      ()=>[i=1, s=getSingleString(i)],
      each [i] < [COLOR=#ff0000]100[/COLOR],  
      each[i=[i]+1, s=getSingleString(i)],
      each [s]),
    Result = Text.Combine(Source, ",")
in
    Result
cheers, hope this helps
 
Another approach. Break the list into groups of 1000 then concatenate the IDs in the resulting tables. My Table1 is a single column with the IDs

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 1000), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Grouped", each _, type table [ID=text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each List.Accumulate(Table.Column([Grouped],"ID"),"",(state, current)=> state & "," & current)),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Added Custom", {{"Custom", each Text.AfterDelimiter(_, ","), type text}}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Text After Delimiter", "IDs", each if [Index]= 0 then [Custom] else "AND " & [Custom]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"IDs"})
in
    #"Removed Other Columns"

Norm
 
Thank you both for your input. In my effort to simplify my example, I misled you in the format of my ID numbers. They are random alphanumeric IDs with no numerical pattern.
However I was able to remedy a working solution. It is a variation of the code that Norm posted above.

Thanks again for your help and if anyone reading this has a similar problem feel free to post below.
 
[Practising on old threads]
There are also the Table.Split and List.Split functions:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][ID],
    tbl = Table.FromList(List.Split(Source,1000), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Result = Table.SelectColumns(Table.AddColumn(tbl, "Split List", each "'" & Text.Combine([Column1],"','") & "'"),{"Split List"})
in
    Result
 
Last edited:
Back
Top