Power Query connection to SSAS Database much slower than Power Pivot Connection

[Wyn Hopkins]

My preference is to always use Power Query for loading data into Power Pivot, however last week I hit an issue where it was taking 3+ minutes to refresh a simple connection to a SSAS database.

I had applied 2 Filters and was returning 50,000 records from a single cube.  This was taking 1-2 mins on my 64 bit Excel 2016  and 3-4 minutes on a 32 bit machine.

I then tried using the Power Pivot connection wizard to do the same process and the refresh time was reduced to 30 seconds in both environments.

Any suggestions on the cause of this?  

I've read about Power Query potentially writing inefficient SQL but is there anything that can be done about it?

[#2]

It's possible that the query we're generating is inefficient. If you are willing to send me the the M query you are using in a private message, I can take a look and see if there is something in it which isn't being folded correctly.

[#3]

Thanks for replying Alex, I'll be back at the client next week and will send you the M code

Cheers

Wyn

[#4]

Here's the code

let
Source = AnalysisServices.Databases("kepionffm1.XYZ.com.au", [TypedMeasureColumns = true]),

#"XYZ FFM" = Source{[Name="XYZ FFM"]}[Data],
Cashflow1 = #"XYZFFM"{[Id="Cashflow"]}[Data],
Cashflow2 = Cashflow1{[Id="Cashflow"]}[Data],

#"Added Items" = Cube.Transform(Cashflow2, {{Cube.AddAndExpandDimensionColumn, "[Account]", {"[Account].[MemberId].[MemberId]"}, {"Account.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[Activities]", {"[Activities].[MemberId].[MemberId]"}, {"Activities.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[MeasureType]", {"[MeasureType].[MemberId].[MemberId]"}, {"MeasureType.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[Project]", {"[Project].[MemberId].[MemberId]"}, {"Project.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Day].[Day]"}, {"Time.Day"}}, {Cube.AddMeasureColumn, "Value", "[Measures].[Value]"}, {Cube.AddAndExpandDimensionColumn, "[Scenario]", {"[Scenario].[MemberId].[MemberId]"}, {"Scenario.MemberId"}}}),

#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([Scenario.MemberId]) = "[Scenario].[MemberId].[Actual]" meta [DisplayName = "Actual"] or Cube.AttributeMemberId([Scenario.MemberId]) = "[Scenario].[MemberId].[Approved WOL]" meta [DisplayName = "Approved WOL"] or Cube.AttributeMemberId([Scenario.MemberId]) = "[Scenario].[MemberId].[Forecast]" meta [DisplayName = "Forecast"]) and (Cube.AttributeMemberId([MeasureType.MemberId]) = "[MeasureType].[MemberId].[Escalated Amount]" meta [DisplayName = "Escalated Amount"]))

in

#"Filtered Rows"