Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I created a query list, but I can't pass it in the Power Query parameter
Query List
Calling a list in the query
Solved! Go to 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.
Proud to be a Super User!
@PhilipTreacy
Thanks your feedback.
I will check this information. I managed to pass the list through a text.combine function.
Thanks for help me.
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.
Proud to be a Super User!
@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.
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.
Proud to be a Super User!
@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"
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.
Proud to be a Super User!
@PhilipTreacy
Thanks your feedback.
I will check this information. I managed to pass the list through a text.combine function.
Thanks for help me.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.