Combining Numbers List in Power Query

[Bobby_Singh]

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.

[v-xinruzhu-msft]

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]

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.

[dufoq3 · Accepted solution]

Hi @Bobby_Singh you can either set privacy level or ignore that in power query settings. Read this article.

[Bobby_Singh]

Thank you @dufoq3 

This solution did help, combining two queries to one.