Power Query - Get Data from AnalysisServices (Cube) - Filter with User-Function result

Question

I have the following problem. I'm getting Data (Excel 365 - Power Query) from a Cube with "Get Data from Analysis Services". -> Selling Qty an Values for filtered years by week.

Every thing is fine if I use Filter, updatetime ca. 3-4 seconds:

    #"Filtered Rows1" = Table.SelectRows(#"Added Items", each 
        (Cube.AttributeMemberId([#"Date.Year (4-4-5)"]) = "[Date].[Year 4-4-5].&["&
         Number.ToText(2019) &"]" meta 
         [DisplayName = Number.ToText(2019)] 

         or Cube.AttributeMemberId([#"Date.Year (4-4-5)"]) = "[Date].[Year 4-4-5].&["&
         Number.ToText(2020) &"]" meta 
         [DisplayName = Number.ToText(2020)] 
         )

Now I like to do that dynamic, so that I can get the years from a cell in excel. I use the following M-Function "fktGetNamedCellValue" for this:

let
    Source = (FieldInput as text) => 

    let Quelle = Excel.CurrentWorkbook(){[Name=FieldInput]}[Content], 
    Inhalt = Number.From(Quelle{0}[Column1])

in  Inhalt
in  Source

I replaced the years in the Filter-Step with the function. The cells are named "cell_Prev_Year" and "cell_Plan_Year" The cells in Excel formated as Numbers (and there are only Numbers in it) The updatetime now -> endless!!!

    #"Filtered Rows1" = Table.SelectRows(#"Added Items", each 
        (Cube.AttributeMemberId([#"Date.Year (4-4-5)"]) = "[Date].[Year 4-4-5].&["&
         Number.ToText(fktGetNamedCellValue("cell_Prev_Year") &"]" meta 
         [DisplayName = Number.ToText(fktGetNamedCellValue("cell_Prev_Year"))] 

         or Cube.AttributeMemberId([#"Date.Year (4-4-5)"]) = "[Date].[Year 4-4-5].&["&
         Number.ToText(fktGetNamedCellValue("cell_Plan_Year")) &"]" meta 
         [DisplayName = Number.ToText(fktGetNamedCellValue("cell_Plan_Year"))] 
         )

If I use a "normal" parameter with the value "2019" or "2020" everything is fine. Only if I use the fktGetNamedCellValue it will not run correctly.

I`ed Trim an Clean the result. Formated it as Text and Number... nothing helped.

I have to use userfriendly Parameter (not set in Power Query) for this, so I hope for some help :)

Best Regards Chris

(PS: I hope u understand my english)

Chris Chris · Accepted Answer · 2020-05-20T13:02:47

I solved this problem as follows.

Since it is not an good idea to use an user-function as parameter to get data from an cube. I think this method disables query-folding or is called a lot of times in the process, I decided to use an power-query parameter.

I change this Parameter with vba by checking the worksheet_change event and calling this sub:

Sub refresh_Parameter(ParameterName As String, ParameterValue As Variant)

Dim strOldFormula As String
Dim strParametersMeta As String

strOldFormula = ThisWorkbook.Queries(ParameterName).Formula
strParametersMeta = Mid(strOldFormula, InStr(1, strOldFormula, "meta"), Len(strOldFormula))

    ThisWorkbook.Queries(ParameterName).Formula = ParameterValue & " " & strParametersMeta

Debug.Print strOldFormula
Debug.Print strParametersMeta

End Sub

The parameters for the sub arte the PARAMETER Name in PowerQuery and the VALUE which should be set. For this the sub extracts the meta-data from the query-formula and combines it with the new Value.

Maybe someone needs this :)

Best regards chris

Power Query - Get Data from AnalysisServices (Cube) - Filter with User-Function result

1 Answers