Forum Discussion

BerlinAlexander's avatar
BerlinAlexander
Helper II
6 years ago
Solved

Inexplicable behavior of dataflows (SQL Analysis Services)

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.
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    5 years ago

    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.

     

1 Reply

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    5 years ago

    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.