Results 1 to 4 of 4

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

  1. #1
    Neophyte awoods8005's Avatar
    Join Date
    Mar 2020
    Posts
    4
    Articles
    0
    Excel Version
    Office 365 Excel

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Acolyte gue's Avatar
    Join Date
    Nov 2018
    Posts
    20
    Articles
    0
    Excel Version
    Office Professional Plus 2016
    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,10) = 0 then "AND " & sID else sID
    in
      result
    
    
    // GetString
    let
        Source = List.Generate(
          ()=>[i=1, s=getSingleString(i)],
          each [i] < 100,  
          each[i=[i]+1, s=getSingleString(i)],
          each [s]),
        Result = Text.Combine(Source, ",")
    in
        Result
    cheers, hope this helps

  3. #3
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    119
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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

  4. #4
    Neophyte awoods8005's Avatar
    Join Date
    Mar 2020
    Posts
    4
    Articles
    0
    Excel Version
    Office 365 Excel
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •