Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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"
Best Regards,
Qiuyun Yu
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |