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"
Community ChampionI 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&"?You're right. It does. I have to use a " before it to get power query to suggest. Then use the suggestion, but it's still giving me an error asking for a token comma expected. If I type ""US"" in that same space, I don't get the error. I really appreciate your help!
- ValtteriN
New idea:
Instead of a custom function we can convert the helperquery into a list and then reference it via &List.First(Example)&