Forum Discussion
Using a DAX query in power query and would like to reference a previous step
MRenwick great Q. I had a similar situation .
Can you try the following and see if it helps
let
Source = Excel.CurrentWorkbook(){[Name="CountryTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}),
Country = #"Changed Type"{0}[Country],
#"Text Filter" = AnalysisServices.Database("SERVER1\CUBE1", "Wholesale",
[Query="/* START QUERY BUILDER */#(lf)
EVALUATE#(lf)SUMMARIZECOLUMNS(#(lf)
'Business Sector'[Country],#(lf)
KEEPFILTERS( TREATAS( {"""&Country&"""}, 'Business Sector'[Country] )),#(lf)
""ERP Net Sls Qty"", [ERP Net Sls Qty]#(lf))#(lf)
/* END QUERY BUILDER */", Implementation="2.0"])
in
#"Text Filter"
or
let
Source = Excel.CurrentWorkbook(){[Name="CountryTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}),
Country = #"Changed Type"{0}[Country],
#"Text Filter" = AnalysisServices.Database("SERVER1\CUBE1", "Wholesale",
[Query="/* START QUERY BUILDER */#(lf)
EVALUATE#(lf)SUMMARIZECOLUMNS(#(lf)
'Business Sector'[Country],#(lf)
KEEPFILTERS( TREATAS( {"""&Text.From(Country)&"""}, 'Business Sector'[Country] )),#(lf)
""ERP Net Sls Qty"", [ERP Net Sls Qty]#(lf))#(lf)
/* END QUERY BUILDER */", Implementation="2.0"])
in
#"Text Filter"
Thank you for the response ValtteriN . I may not be doing something correctly. I've tried a few different ways based on what you said. Is this how the query should look?
let
Source = () => let
Source = Excel.CurrentWorkbook(){[Name="CountryTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}),
Country = #"Changed Type"{0}[Country]
in
Country,
#"Text Filter" = AnalysisServices.Database("SERVER1\CUBE1", "Wholesale",
[Query="/* START QUERY BUILDER */#(lf)
EVALUATE#(lf)SUMMARIZECOLUMNS(#(lf)
'Business Sector'[Country],#(lf)
KEEPFILTERS( TREATAS( {Country}, 'Business Sector'[Country] )),#(lf)
""ERP Net Sls Qty"", [ERP Net Sls Qty]#(lf))#(lf)
/* END QUERY BUILDER */", Implementation="2.0"])
in
#"Text Filter"
- ValtteriN
Community Champion
I was thinking that you could divide the query in two parts. The custom function would be a separate query that you call in your query.
e.g.I see what you're saying. That's a great idea. I'm getting an error, token comma expected, when trying to call the function. I've named my function query FunctionCountry. In the below, that's what is underlined in red as the error. I've tried it with and without "" around it.
let
#"Text Filter" = AnalysisServices.Database("SERVER1\CUBE1", "Wholesale",
[Query="/* START QUERY BUILDER */#(lf)
EVALUATE#(lf)SUMMARIZECOLUMNS(#(lf)
'Business Sector'[Country],#(lf)
KEEPFILTERS( TREATAS( {"FunctionCountry()"}, 'Business Sector'[Country] )),#(lf)
""ERP Net Sls Qty"", [ERP Net Sls Qty]#(lf))#(lf)
/* END QUERY BUILDER */", Implementation="2.0"])
in
#"Text Filter"- ValtteriN
Hmm,
Powerquery should suggest the custom function without (). But since it is within quotes that wouldn't work. How about this: "&Example&"?
