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 ChampionHmm,
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)&