Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Find articles, guides, information and community news

Most Recent
Community Support
Community Support


With my experience of using Power BI, many users want to insert a sum row into the data table instead of using the grand total feature in the table/matrix visual. However, Power BI does not support inputting rows into columns by DAX method. Therefore, I would like to introduce the way to insert rows in query editor with further extension of adding multiple rows to sum up the previous N days’ data. It is important to note that the method is only suitable if you would like to insert roles to the existing column. As the result, the date hierarchy would become invalid that may affect the creation of report, this is because adding the sum rows would change the column type from Date/Time to Text.


Community Support
Community Support


In query editor, you can use parameter as a filter option. 

You can use parameters when defining connection properties. For example, if you retrieve data from SQL Server, you can use one parameter for the SQL Server instance and one parameter for the target database. And you can also use parameter to SQL queries by  Value.NativeQuery(). 

You can create a new function from a query and reference parameters as the arguments of your custom function. 


In this article, I will show you how to use column (the column can be obtained from other queries) as parameter to get data from API. 


API Used: 


Detailed steps: 


Create a custom function (In general, we need to covert query to function in power query without using M script: 


  • Convert query to function 

Select New Parameter from New Parameters menu in Query Editor:  



Create a parameter with all default selections, set the Current Value to  2020 and name it as YEAR: 



Create a query in Power BI Desktop by Get Data 



Type the API in the url: 

Dedmon Dai4.png


Select Table0 with holiday information and click Transform Data: 

Dedmon Dai5.png


Change the original query to use the parameter: 

Dedmon Dai6.png 


Replace 2020 with parameters by adding new part and selecting the parameter:  

 Dedmon Dai7.png

Convert query to function by right-clicking Query and selecting Create Function: 

Dedmon Dai8.png


Enter a name for new created function: 

 Dedmon Dai9.png


This completes the creation of our function without using M script: 

Dedmon Dai10.png


  • Use the column as parameter for the custom function to combine the query with the same data structure: 


Create a new table with Year column: 

 Dedmon Dai11.png


Click ‘Invoke Custom Function’ in ‘Add Column’ menu to use the custom function: 

Dedmon Dai12.png 


Select your custom function and select Column Name in the parameter:

Dedmon Dai13.png 


Select the Year column in the new table : 

Dedmon Dai14.png


Expand all the columns in the Holiday Function column and you will get all the holidays in 2018, 2019, and 2020: 

Dedmon Dai15.png




This article describes how to create custom functions using the GUI. And use column as the parameter of the function to combine queries with similar data structures 

Please refer to the attachment for details. 


Author: Dedmon Dai

Reviewer:  Kerry and Ula



Helpful resources

Join Blog
Interested in blogging for the community? Let us know.