Inexplicable behavior of dataflows (SQL Analysis Services)

[BerlinAlexander]

I have a dataflow with several queries from one DB and it works fine.

I added another query from the same DB and I simply can't make it work (i.e. the preview displays correct data, but any refresh fails). In the meantime I tried copying the query into a new dataflow in order to be sure it's about it and it is.

My advanced query is as follows. I created it by copying it from PowerBI desktop - it works there. Initially I created it in SQL Server Management Studio (mdx) and it shows the correct results there:

let

  Source = AnalysisServices.Database("asazure://[adress]/[DB]", "model", [Query="SELECT NON EMPTY #(lf)    {[Product].[Source].[Source]}#(lf)#(tab)ON 0, #(lf)#(tab)NON EMPTY#(lf)    CROSSJOIN(#(lf)#(tab){[Product].[Brand Name].[Brand Name]},#(lf)#(tab)[Product].[EAN Product Code].[EAN Product Code],#(lf)#(tab)[Product].[Product SKU Code].[Product SKU Code],#(lf)#(tab)[Product].[Segment Sub Group Description].[Segment Sub Group Description],#(lf)#(tab)[Product].[Sub Category Desc].[Sub Category Desc],#(lf)#(tab)[Product].[Product SKU Description].[Product SKU Description])#(lf)#(tab)ON 1#(lf)FROM [model]#(lf)WHERE ({ [Product].[Sector Description].[Sector1], #(lf)[Product].[Sector Description].[Sector2],#(lf)[Product].[Sector Description].[Sector3], #(lf)[Product].[Sector Description].[Sector4]})#(lf)#(lf)", Implementation="2.0"]),

  #"Transform columns" = Table.TransformColumnTypes(Source, {{"[Product].[Source].&[EDW]", type text}, {"[Product].[Source].&[Not Available]", type text}, {"[Product].[Source].&[OTHER]", type text}}),

  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"[Product].[Source].&[EDW]", null}, {"[Product].[Source].&[Not Available]", null}, {"[Product].[Source].&[OTHER]", null}})

in

  #"Replace errors"

The line marked red gets added authomatically even if I delete it.

The parts marked green weren't initially there. I added them since my working queries have them. I thought that was the problem.

I would appreciate any suggestions.

[v-diye-msft · Accepted solution]

Hi @BerlinAlexander 

Please note that, from the article , we know that the Azure analysis Services instead of SQL Server Analysis Services connector is supported in dataflow. That's should be reason why it doesn't work.

While your demand is a good idea, you can post your new idea in Idea Forum , to improve the Power BI.

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.