I created a query list, but I can't pass it in the Power Query parameter

[Anonymous]

I created a query list, but I can't pass it in the Power Query parameter
Query List

Calling a list in the query

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

The function won't let you pass a list into it so if you can't modify the function, you'll have to modify how you call it.

What I would do is:

1. Convert the list to a table.

2. Rename the column Name - this is to match the function call in my code below.  You can call it anything you like really, just adjust the function call accordingly.

3. In the same query (where you've just created a table from the list), add this line before the in keyword

FunctionCall = Table.AddColumn(#"PreviousStep", "FunctionResult", each dbo_EpmQueryRawFunction (-3, #datetime(2020, 10, 27, 0, 0, 0), #datetime(2020, 10, 28, 0, 0, 0), 0, true, [Name])

This will give you a new column with the results from the function for every item in the list.

Your query should now look something like this

let
    Source = {"AAA", "BBB", "CCC"},  // Example of the list of names

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Name"}}),
    
    FunctionCall = Table.AddColumn(#"Renamed Columns", "FunctionResult", each dbo_EpmQueryRawFunction (-3, #datetime(2020, 10, 27, 0, 0, 0), #datetime(2020, 10, 28, 0, 0, 0), 0, true, [Name]))

in

    FunctionCall

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[PhilipTreacy]

Hi @Anonymous 

The error is telling you it can't convert a List to Text.

Sorry but I don't speak Portuguese and I don't know what function you are trying to use.  When I use Google to translate Consulta it tells me it means Query.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

@PhilipTreacy Thanks

I am extracting data from a Sql Server database through function, but I can only extract it by filling in the parameters.
In the @node field, I need to pass a list but the field only accepts one.
So I created another table with the list.

Now I need to make this parameter pass the list to the on-going vehicles one by one.

[PhilipTreacy]

Hi @Anonymous 

Judging by the error, that function only takes a single text value as the node parameter. It doesn't accept a list.

You will either need to modify the function to accept a list, or just pass a single text value.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

@PhilipTreacy 

As would be the function to pass a list, follows the original

let
Fonte = Sql.Databases("10.240.21.10"),
EPM_Database = Fonte{[Name="EPM_Database"]}[Data],
dbo_EpmQueryRawFunction = EPM_Database{[Schema="dbo",Item="EpmQueryRawFunction"]}[Data],
#"Função Invocadadbo_EpmQueryRawFunction1" = dbo_EpmQueryRawFunction (-3, #datetime(2020, 10, 27, 0, 0, 0), #datetime(2020, 10, 28, 0, 0, 0), 0, true,
Lista)
in
#"Função Invocadadbo_EpmQueryRawFunction1"

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

The function won't let you pass a list into it so if you can't modify the function, you'll have to modify how you call it.

What I would do is:

1. Convert the list to a table.

2. Rename the column Name - this is to match the function call in my code below.  You can call it anything you like really, just adjust the function call accordingly.

3. In the same query (where you've just created a table from the list), add this line before the in keyword

FunctionCall = Table.AddColumn(#"PreviousStep", "FunctionResult", each dbo_EpmQueryRawFunction (-3, #datetime(2020, 10, 27, 0, 0, 0), #datetime(2020, 10, 28, 0, 0, 0), 0, true, [Name])

This will give you a new column with the results from the function for every item in the list.

Your query should now look something like this

let
    Source = {"AAA", "BBB", "CCC"},  // Example of the list of names

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Name"}}),
    
    FunctionCall = Table.AddColumn(#"Renamed Columns", "FunctionResult", each dbo_EpmQueryRawFunction (-3, #datetime(2020, 10, 27, 0, 0, 0), #datetime(2020, 10, 28, 0, 0, 0), 0, true, [Name]))

in

    FunctionCall

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous · Accepted solution]

@PhilipTreacy 
Thanks your feedback.
I will check this information. I managed to pass the list through a text.combine function.
Thanks for help me.