cancel
Showing results for 
Search instead for 
Did you mean: 

Find articles, guides, information and community news

Most Recent
Helper II
Helper II

Facebook Ads are an essential part of many online marketing campaigns. Although some argue the importance of this platform to promote products and services, it is still a vast market. If you decide to spend some money advertising on Facebook, you would like to get most of Facebook's analytic capability and leverage the pure range of data you can retrieve.

Read more...

Super User IV
Super User IV

When Data has been provided between the start date and end date. Output needs to be viewed by month or period. 

Read more...

Super User I
Super User I

Try this method when a normal calendar table will not "cut the mustard".

One of the first concepts I heard of when I began my journey of using Power BI was creating and using a 'Date' table. A few short years ago, there wasn't nearly the amount of documentation as there is now; we are very fortunate. What you will find is there are numerous ways to accomplish this goal, and this is one way to solve a problem.

Read more...

Administrator
Administrator

Please see important updates to Ideas.PowerBI.com

Read more...

Community Support
Community Support

This is a blog about  K-nearest neighbors (KNN) algorithm in power bi.

Read more...

Microsoft
Microsoft

Scenario: 

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:  

 

https://holidayapi.com/countries/cn/2020 

 

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:  

V-lianl-msft_0-1605496589680.png

 

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

 Dedmon2.png

 

Create a query in Power BI Desktop by Get Data 

V-lianl-msft_2-1605496589681.png

 

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

 

Summary 

 

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

 

Read more...

Helpful resources

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