Using a DAX query in power query and would like to reference a previous step

[MRenwick]

Hello,

I'm using a DAX query to access analysis services in power query.  I'd like to reference a previous step as a filter.  In the below example, Instead of "US" in the TREATAS function, I'd like to use the value pulled in the Country step above.  How can I reference that step?

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( {""US""}, 'Business Sector'[Country] )),#(lf)
""ERP Net Sls Qty"", [ERP Net Sls Qty]#(lf))#(lf)
/* END QUERY BUILDER */", Implementation="2.0"])
in
#"Text Filter"

[smpa01 · Accepted solution]

@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"

[MRenwick]

The triple quotes with the & worked.  Thank you very much.  I had tried it with single and double quotes but not triple.  Awesome!

[MRenwick]

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]

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.

[MRenwick]

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&"?

[MRenwick]

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

[ValtteriN]

Hi,

I think you can create a custom function based on the steps prior to #"Text filter" and then reference that in your query. So something like this:

let
Source = () => let
Source = Excel.CurrentWorkbook(){[Name="CountryTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}),
Country = #"Changed Type"{0}[Country]


in
Country
in
Source

Hopefully this helps and if it does consider accepting this as a solution!