cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mranjank Frequent Visitor
Frequent Visitor

How to refer a parameter in the SQL query and decode to produce static values

I have a query which looks likes this

select quarter, some values from table

Result shows quarter as 0,1,2,3,4,5,6 - basically 13 financial quarter and its values

 

I don't know the starting quarter so I am using a parameter which user will enter. When the template is opened user enter 2Q and year as 2016. From there on I want to decode the "quarter" column as 0 to 2Q2016, 1 to 3Q2016, 2 to 4Q2016, 3 to 1Q2017

 

how do I do it?

couldn't find any material on how to refer the entered parameter value in my query

using Power BI desktop to create the report

3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

Re: How to refer a parameter in the SQL query and decode to produce static values

Hi @mranjank,

 

When we use desktop connect to SQL Server database, we can pass parameters to T-SQL query. See: Pass Parameters to SQL Queries. Also we can use Query Parameters in Query Editor to filter the data: Deep Dive into Query Parameters and Power BI Templates.

 

In your scenario, what do you mean about the "decode the "quarter" column as 0 to 2Q2016, 1 to 3Q2016, 2 to 4Q2016, 3 to 1Q2017"? Can you show us some sample data and logic to return the desired results?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mranjank Frequent Visitor
Frequent Visitor

Re: How to refer a parameter in the SQL query and decode to produce static values

Hi @v-qiuyu-msft,

Thank you for your reponse. Here is an example

I define this query in dekstop query editor

Select Period, Profit from Fin_table

 sample data below

Period          Profit

--------------------

0                  10

1                  10

2                  10

3                  10

4                  10

 

Peremeter Quarter and Year 

 

If user enters Quarter as 2Q and Year as 2016. The sameple data should be converted to below and present in the report layout

Period                 Profit

-------------------------

2Q2016                  10

3Q2016                  10

1Q2017                  10

2Q2017                  10

3Q2017                  10

Moderator v-qiuyu-msft
Moderator

Re: How to refer a parameter in the SQL query and decode to produce static values

Hi @mranjank,

 

Sorry for the late response.

 

In my opinion, it's better to have columns Year, Quarter in source table, so that in the Power BI desktop, you can create query parameters to filter them.

 

For your requirement, you can write the Power Query like below:

 

let
    Source = Sql.Databases("sql2016ga"),
    qiuyun = Source{[Name="qiuyun"]}[Data],
    dbo_test1129 = qiuyun{[Schema="dbo",Item="test1129"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo_test1129, "Custom", each Text.Range(Quarter,0,1)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each [Period] + [Custom]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1]<4 then [Custom.1] else ([Custom.1]-3)),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "quarter"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns", "year", each if [Custom.1]<4 then Year else Year + 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"quarter", type text}, {"year", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type1", "Custom.2", each [quarter]&"Q"&[year]),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom4",{{"Custom.2", "New Quarter"}})
in
    #"Renamed Columns1"

 

q2.PNG

 

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.