Reference Excel cell in powerpivot sql query

[Kalle123]

Hi!

I wonder if there is any way to have a dynamic selection in the SQL in PowerPivot.

I have following Query right now.

SELECT NON EMPTY { [Measures].[CB Pax Cum TY] } ON COLUMNS, NON EMPTY { ([Book week].[Book week].[Book week].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( { [Destination].[Dest code].&[ABC] } ) ON COLUMNS
FROM ( SELECT ( { [Reservation system].[Reservation system].&[XXX] } ) ON COLUMNS
FROM ( SELECT ( { [Book week].[Book week].&[201608], [Book week].[Book week].&[201609], [Book week].[Book week].&[201610] } ) ON COLUMNS
FROM [XXX])))

WHERE ( [Reservation system].[Reservation system].&[XXX], [Destination].[Dest code].&[ABC] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

And it delivers following table

Query
Book weekBook weekBook week_______MeasuresCB Pax Cum TY
2016-08__________________________300
2016-09__________________________400
2016-10__________________________500

Instead of having 201608, 201609, 201610 listed as book week I want it to reference to three cells in excel. I also want to do the same for the Dest code; instead of ABC I want it to reference to an Excel file instead. I have named my cells in Excel to Bookweek1, Bookweek2, Bookweek3 and Dest. Is it possible to reference the cells instead?

I have also imported the information as a Table in PowerPivot. Is it possible to make a join between Table2 (se below) and the Query? The join should state that it should only show information that have the same book week and Dest code as in Table2. I have tried to make an Inner join but I just can't make it work...

Table2
Dest____Bookweek
ABC_____2016-08
ABC_____2016-09
ABC_____2016-10

Is there anyone out there that can help me?

Best regards!

 

[Kalle123]

Hi!

I tried with both this code

let
Function = (Bookweek_, Dest_) =>

let
Source = AnalysisServices.Database("XXX.int", "XXX-Cubes", [TypedMeasureColumns=true]),
YYY = Source{[Id="YYY"]}[Data],

#"YYY Weekly Reports1" = YYY{[Id="YYY Weekly Reports"]}[Data],
 #"Added Items" = Cube.Transform(#"YYY Weekly Reports1", {{Cube.AddAndExpandDimensionColumn, "[Book week]", {"[Book week].[Book week].[Book week]"}, {"Book week.Book week"} and "[Destination]", {"[Destination].[Dest code].[Dest code]"}, {"Destination.Dest code"}} ,{Cube.AddMeasureColumn, "CB1 SEK Cum TY", "[Measures].[CB1 SEK CUM TY]"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Added Items3",{{"Destination.Dest code", type text}, {"CB1 SEK Cum TY", Int64.Type}, {"Book week.Book week", type text}}),
Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&["&Bookweek_&"]" and Cube.AttributeMemberId([Destination.Dest Code]) = "[Destination].[Dest code].&["&Dest_&"]")

in
Filter,
    Source = Excel.Workbook(File.Contents("C:\Users\1134\Documents\Mail Mall.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Bookweek", type text}, {"Dest", type text}}),
CallFunction = Table.AddColumn(#"Changed Type", "Custom", each Function([Bookweek], [Dest]))

in
CallFunction

and this code (which has now formatting specifications in it for the first Source (See #Change type above)

[CODE]let
Function = (Bookweek_, Dest_) =>

let
Source = AnalysisServices.Database("XXX.int", "XXX-Cubes", [TypedMeasureColumns=true]),
YYY = Source{[Id="YYY"]}[Data],

#"YYY Weekly Reports1" = YYY{[Id="YYY Weekly Reports"]}[Data],
 #"Added Items" = Cube.Transform(#"YYY Weekly Reports1", {{Cube.AddAndExpandDimensionColumn, "[Book week]", {"[Book week].[Book week].[Book week]"}, {"Book week.Book week"} and "[Destination]", {"[Destination].[Dest code].[Dest code]"}, {"Destination.Dest code"}} ,{Cube.AddMeasureColumn, "CB1 SEK Cum TY", "[Measures].[CB1 SEK CUM TY]"}}),

Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&["&Bookweek_&"]" and Cube.AttributeMemberId([Destination.Dest Code]) = "[Destination].[Dest code].&["&Dest_&"]")

in
Filter,
    Source = Excel.Workbook(File.Contents("C:\Users\1134\Documents\Mail Mall.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Bookweek", Int64.Type}, {"Dest", type text}}),
CallFunction = Table.AddColumn(#"Changed Type", "Custom", each Function([Bookweek], [Dest]))

in
CallFunction

But I still get.

Expression.Error: We cannot convert a value of type List to type Logical.
Details:
Value=List
Type=Type

Soon I am starting to think that this is a lost cause..

 

[ImkeF]

I was so optimistic in my previous mail because I was able to replicate the error-message - hence thinking we were pretty close. (I've used this technique on many different tabular models on different servers, to you can be sure that it works - principally).

Of course: Understandable if you don't want to continue, but if you do, I'd recommend to debug the following:
Copy the code for the Cube-query and paste it into the advanced editor of a new query. It should run until the "Filter" step, where it would error for the parameter:

let
Source = AnalysisServices.Database("XXX.int", "XXX-Cubes", [TypedMeasureColumns=true]),
YYY = Source{[Id="YYY"]}[Data],

#"YYY Weekly Reports1" = YYY{[Id="YYY Weekly Reports"]}[Data],
 #"Added Items" = Cube.Transform(#"YYY Weekly Reports1", {{Cube.AddAndExpandDimensionColumn, "[Book week]", {"[Book week].[Book week].[Book week]"}, {"Book week.Book week"} and "[Destination]", {"[Destination].[Dest code].[Dest code]"}, {"Destination.Dest code"}} ,{Cube.AddMeasureColumn, "CB1 SEK Cum TY", "[Measures].[CB1 SEK CUM TY]"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Added Items3",{{"Destination.Dest code", type text}, {"CB1 SEK Cum TY", Int64.Type}, {"Book week.Book week", type text}}),
Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&["&Bookweek_&"]" and Cube.AttributeMemberId([Destination.Dest Code]) = "[Destination].[Dest code].&["&Dest_&"]")

in
Filter

At least then you know that the Filter-step is actually the cause. Maybe it's the previous step instead #"Added Items". Then you could just correct this step there: Easiest way by deleting the step and dragging in the needed fields again. Then you copy the code back to the other query.

 

[Kalle123]

Oh I never want to give up. I just feel bad that you have to help me so much!

So now I have tried to paste in only the Cube-query and I get following message: Expression error: The name 'Bookweek_' wasn't recognized. Make sure it's spelled correctly. Which is understandable since I havn't defined it anywhere in the query.

I then tried to change &["&Bookweek_&"]" to [201610] and ["&Dest_&"] to [ABC] but then I get the following error message:
Expression.Error: We cannot convert a value of type List to type Logical.
Details:
Value=List
Type=Type

I then tried to change the filter to:
Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek] = "2016-10" ) and Cube.AttributeMemberId([Destination.Dest Code] = "AYT"))
But I still get the error message: We cannot convert a value of type List to type Logical.

The reason why i tried this is that if I filter my data from the Qube in the Query then I get the following Filter but the code looks like this:
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Destination.Dest code] = "ABC") and ([Book week.Book week] = "2016-10"))

 

[ImkeF]

After we’ve edited the code at multiple places, it could now become tedious to find the error.

So I’d recommend rebuilding that part: Start a new query, access your cube, select all needed fields and use hardcoded filters for every filter that has to be parametrised. This should run.

Then you “just” replace your hardcoded filtervalues by "&Bookweek_&" and “&Dest_&”

Copy that code & replace it into the big one.