Pivot breaks SSAS query folding

[plomb]

Is anyone able to explain me why the following steps break my SSAS native query?

let
Source = AnalysisServices.Databases("xxxx", [TypedMeasureColumns=true, Implementation="2.0"]),
#"SSAS Profit And Loss Actuals" = Source{[Name="SSAS DW Profit And Loss Actuals New"]}[Data],
#"SSAS DW Profit And Loss Actuals1" = #"SSAS DW Profit And Loss Actuals New"{[Id="SSAS DW Profit And Loss Actuals new"]}[Data],
#"SSAS DW Profit And Loss Actuals2" = #"SSAS DW Profit And Loss Actuals new1"{[Id="SSAS DW Profit And Loss Actuals new"]}[Data],
#"Added Items" = Cube.Transform(#"SSAS DW Profit And Loss Actuals new2",
{
{Cube.AddMeasureColumn, "Actual", "[Measures].[Actual]"}
}),
#"Added Items1" = Cube.Transform(#"Added Items",
{
{Cube.AddAndExpandDimensionColumn, "[Item Dimension]", {"[Item Dimension].[Item Commercial Filter].[Item Commercial Filter]"}, {"Item Dimension.Item Commercial Filter"}}
}),
#"Pivoted Column" = Table.Pivot(#"Added Items1", List.Distinct(#"Added Items1"[#"Item Dimension.Item Commercial Filter"]), "Item Dimension.Item Commercial Filter", "Actual", List.Sum)
in
#"Pivoted Column"

[plomb]

Hi v-stephen,

it's SSAS so I cannot provide you with dummy data. However the issue is quite simple - after pivoting the native query is broken. While I can still have it done on client side, I would like the pivoting step to happen on server side. Is this possible?

[Anonymous]

Hi @plomb ,

Sorry I can't reproduce it. Please tell me what error message did you get?

Or present your table with dummy data and share me with expected result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.