Forum Discussion
Year selection in Query
Hello, how is it possible to select direct in the query the years 2019&2020?
Thank you
= Cube.Transform(BOOST_CRM_Sales1, { {Cube.AddAndExpandDimensionColumn, "[CRM Customer]", {"[CRM Customer].[Customer Code].[Customer Code]"}, {"03 - Customer.Customer Code"}}, {Cube.AddAndExpandDimensionColumn, "[Business Partner]", {"[Business Partner].[Business Partner Code].[Business Partner Code]"}, {"08 - Business Partner.Business Partner Code"}}, {Cube.AddAndExpandDimensionColumn, "[CRM Material]", {"[CRM Material].[Signature Code].[Signature Code]", "[CRM Material].[European Article Number].[European Article Number]", "[CRM Material].[Material Code].[Material Code]"}, {"06 - Material.Signature Code", "06 - Material.European Article Number", "06 - Material.Material Code"}}, {Cube.AddAndExpandDimensionColumn, "[CRM Campaign]", {"[CRM Campaign].[Campaign Level Code].[Campaign Level Code]"}, {"07 - Campaign.Campaign Level Code"}}, {Cube.AddAndExpandDimensionColumn, "[Sales Document]", {"[Sales Document].[Purchase Order Type].[Purchase Order Type]", "[Sales Document].[Sales Order Reason].[Sales Order Reason]", "[Sales Document].[Sales Order Type].[Sales Order Type]"}, {"09 - Sales Document.Purchase Order Type", "09 - Sales Document.Sales Order Reason", "09 - Sales Document.Sales Order Type"}}, {Cube.AddAndExpandDimensionColumn, "[Sales Order Number]", {"[Sales Order Number].[Sales Order Number].[Sales Order Number]"}, {"13 - Sales Order Number.Sales Order Number"}}, {Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Calendar Year].[Calendar Year]", "[Time].[European Week Hierarchy].[European Week]", "[Time].[Month Year].[Month Year]"}, {"15 - Time.Calendar Year", "15 - Time.European Week", "15 - Time.Month Year"}}, {Cube.AddMeasureColumn, "Act. Invoiced Sales", "[Measures].[Act. Invoiced Sales]"}, {Cube.AddMeasureColumn, "Act. Sold Units", "[Measures].[Act. Sold Units]"}, {Cube.AddMeasureColumn, "Total Portfolio (Net Sales value) (Open+DNI+Invoiced)", "[Measures].[Total Portfolio (Net Sales value) (Open+DNI+Invoiced)]"}, {Cube.AddMeasureColumn, "Total Portfolio Quantity (Open+DNI+Invoiced)", "[Measures].[Total Portfolio Quantity (Open+DNI+Invoiced)]"} })
Hi de_ppd
in query editor, you can create query parameter and configure a list of values(parameters you need) previously. For mode details, I would suggest you refer to doc below:
https://docs.microsoft.com/en-us/power-query/power-query-query-parameters
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/
3 Replies
de_ppd Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hello Greg_Deckler,
my target is that I do not need to load all available years in my query. I want to load only the time.calendar years 2019 & 2020. Because the data is to much and I get timeouterrors for my queries. Because now I load all years and filter after that in PowerBI Dekstop the 2 years. But it is to much for refresh without any timeouterrors.
Best regardsHi de_ppd
in query editor, you can create query parameter and configure a list of values(parameters you need) previously. For mode details, I would suggest you refer to doc below:
https://docs.microsoft.com/en-us/power-query/power-query-query-parameters
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/
