Forum Discussion

Bobby_Singh's avatar
Bobby_Singh
New Member
2 years ago
Solved

Combining Numbers List in Power Query

Hi All,

Posting my first question to community. Thank you for your help in advance.

 

I have facing a unique problem where i have a list of numbers Data type, for example as follwong

List

10022701

10023720

11193033

 

and this a list of 150 records and the Data type is Number.

Now I need to combine these numbers to pass it to the Snowflake query

End result I need is '10022701' , '10023720' , '11193033'

 

I know one way to do this is to convert this list to Text type first, Number.ToText and then use Text.Combine({ Passlist, " ', "})

 

But this way final result numbers are of text type which snowflak is not accepting.

 

I want to combine Number list only as '10022701' , '10023720' , '11193033' so that accessence of the numbers or not Text type but number type to retrive the data from snowflake to Power Query.



Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Bobby_Singh 

    You can put the following code to advanced editor in power query first.

    let
    a={10022701,10023720,11193033},
    b=List.Transform(a,each "'"&Text.From(_)&"'"),
    c=Text.Combine(b,",")
in
    c

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Bobby_Singh's avatar
      Bobby_Singh
      New Member
      2 years ago

      Hi @v-xinruzhu-msft 

       

      I tried Text.Combine function, for example in aforementioned solution, if you take List C

      Items_List = c,

      Source = Select * from ABC_Table
      where
      INVENTORY_ITEM_ID in (" & Items_List & " )

      ____________________________________________________________

       

      then this code throw me following error

       

      Formula.Firewall: Query 'Inventory Data (2)' (step 'Renamed Columns') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.