# Reference Excel cell in powerpivot sql query



## Kalle123 (Mar 15, 2016)

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!


----------



## ImkeF (Mar 15, 2016)

you can do that if load your data through Power Query into the Power Pivot datamodel instead of directly.

equal filters filter are easiest done by merging with JoinKind.Inner as described in these articles:

Use Timeline or Slicers to filter your Power Query import – The BIccountant

Want to filter a PivotTable based on an external range or list? – The BIccountant...


But you have to make sure *not *to write SQL-statements by hand as you did in Power Pivot, but use the power query userinterface or M-code instead. Otherwise the query will not fold - meaning that the filtering will not be done at the server but in Excel instead (after all you data will be loaded to Excel).
For details see here: Filter SQL-server queries with Excel-tables: Query folding limitations and bug workaround – The BIccountant


----------



## ImkeF (Mar 15, 2016)

... my answer appeared 2 times - 2nd deleted


----------



## Kalle123 (Mar 15, 2016)

Hi Imke!

Thank you for the Reply! 

I have tried connecting to the database via Power Query but I have troubles to connect to it correctly. I have sent the matter on to the IT-department to see if they can figure it out. Meanwhile, is there any other way to solve it without using Power Query or is it a must to connect to Power Query?

Best regards


----------



## Kalle123 (Mar 15, 2016)

Edit: I have now spoken to the IT-department and unfortunatly do not anyone know how to connect to our database via Power Query. The information I have right now is collected from a cube in Analysis Services. So if the matter can be solved without Power Query, I would be really glad!


----------



## Kalle123 (Mar 15, 2016)

Sorry for the spamming! But I think I just managed to get the data in to Power Query! Woohoo 

BUT, Now comes the problem. I don't understand really how to change the query so it fits my query.. Can you please help me?

Your suggestion was:
let
// 2 Performing the filter operation in a function
function = (group)=>
let
Quelle = Sql.Databases("SQL_Server_Name"),
Database_Name = Quelle{[Name="Database_Name"]}[Data],
dbo_Data = Database_Name {[Schema="dbo",Item="Daten"]}[Data],
Result = Table.Buffer(Table.NestedJoin(dbo_Data,{"KeyId"},Table.SelectRows(FilterTable, each [Group] = group),{"KeyId"},"NewColumn",JoinKind.Inner))
in
   Result,

// 1 Splitting filter table ("Tabelle1") into chuncks of 200 lines and add index
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
ChgType = Table.TransformColumnTypes(Quelle,{{"KeyId", type number}}),
AddIndex = Table.AddIndexColumn(ChgType, "Index", 1, 1),
FilterTable = Table.AddColumn(AddIndex, "Group", each Number.RoundUp([Index]/200)),

// 3 Main query: Create table with groups to call function from, call function and expand (thereby stitching the results back together)
Source = {1..Number.RoundUp(List.Max(FilterTable[Group]))},
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddFunctionCol = Table.AddColumn(ConvertToTable, "Custom", each function([Column1])),
ExpandResults = Table.ExpandTableColumn(AddFunctionCol, "Custom", {"KeyId", "Datum", "Wert"}, {"KeyId", "Datum", "Wert"})
in
   ExpandResults




Right Now I have this information:

let
    Source = AnalysisServices.Database("XXX.prod.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"}}, {Cube.AddMeasureColumn, "CB1 Cum TY", "[Measures].[CB1 SEK TY]"}})
in
    #"Added Items"


And I also have the other Query

let
    Source = Excel.Workbook(File.Contents("NNN.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Dest", type text}, {"Bookweek", type date}})
in
    #"Changed Type"

Sorry, I am such a Newbie! 
Thank you!


----------



## ImkeF (Mar 15, 2016)

Congrats - you're a very talented Newbie!

Apologies for not having spotted that you were using MDX here. So if you're connecting to a cube you need to use a different technique:
Perfect Analysis Services (SSAS) reports in Excel using Power Query – The BIccountant

You need to create a function where the filter parameters are set that will be passed over from your dynamic Excel-Filter-Table ("Table2") and call this from your Table2. This is really tough for beginners, so I've tried to write the code as complete as possible:



```
[B] // This is the part that transforms your already existing cube-query to a function
let
Function = (Bookweek_, Dest_) =>
[/B] 
// Here starts your existing cube-query
let
Source = AnalysisServices.Database("XXX.prod.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"}}, {Cube.AddMeasureColumn, "CB1 Cum TY", "[Measures].[CB1 SEK TY]"}}),

[B]//Added: Filter containing the parameters, "&ParameterNameDefinedAbove&"[/B]
Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&[[B]"&Bookweek_&"[/B]]" and ... corresponding expression for your destination that you haven't expanded yet .....)

in
Filter

// Your already existing 2nd query
Source = Excel.Workbook(File.Contents("NNN.xlsx"), null, true),
 Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Dest", type text}, {"Bookweek", type date}}),

//Added: Function call: The parameters from the columns are passed to the function
CallFunction = Table.AddColumn(#"Changed Type", "Custom", each Function([Bookweek], [Dest])
in
CallFunction
```
This is one query that you should copy and paste into the advanced editor of a new query. It should return your FilterTable (Table2) with an additional column "Custom" where the all the corresponding rows from your flattenend Cube sit in. You just have to click on the arrow-buttons to expand the fields you need. This should fold back to the server.

You might need to change the format of "Bookweek" in your Table2 so that it matches the cube's.

Just post the error-message you might receive - we could then work from there!


----------



## Kalle123 (Mar 16, 2016)

Hi Imke!

Your are a star!

So I have tried the Query and I get the error message: Token Comma Expected. 

It markes that the line Source is wrong.
in
Filter
*Source = Excel.Workbook(File.Contents("C:\Users\XXX"), null, true),* 
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Dest", type text}, {"Bookweek", Int64.Type}})

The Table2 is located in my working file. I don't know if this affects anything. 

I tried to remove filter before Source and then I got the error message on CallFunction instead. 

Additional information. The Destination information is following: = Cube.Transform(#"Added Items", {{Cube.AddAndExpandDimensionColumn, "[Destination]", {"[Destination].[Dest code].[Dest code]"}, {"Destination.Dest code"}}})
If I filter on Bookweek manualy I get following filter: = Table.SelectRows(#"Added Items1", each (Cube.AttributeMemberId([Book week.Book week]) = "[Book week].[Book week].&[201608]" meta [DisplayName = "2016-08"]))
If I filter on Destination manualy I get following filter: = Table.SelectRows(#"Added Items1", each (Cube.AttributeMemberId([Destination.Dest code]) = "[Destination].[Dest code].&[ABC]" meta [DisplayName = "ABC"]))

I added a blank Query and went to the Advanced Editor and pasted in your code. I did some minor changes. I added the Destination Code in the filter
Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&["&Bookweek_&"]" and [Destination.Dest Code] = "[Destination].[Dest code].&["&Dest_&"]")
I also changed the Bookweek type to Int64.Type . I don't know if this is correct. 
{"Bookweek", Int64.Type}})


----------



## ImkeF (Mar 16, 2016)

Wow - an error message that actually says what it needs - we like that 

in
Filter*,*
Source = Excel.Workbook(File.Contents("C:\Users\XXX"), null, true), 

In case it's hard to spot: After the Filter!
Rest looks pretty good!

For the learning curve: If the table already sits in your workbook, then your original code isn't needed here:

let
 Source = Excel.Workbook(File.Contents("NNN.xlsx"), null, true),
 Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Dest", type text}, {"Bookweek", type date}})

You could instead replace it by:

Source = *Excel.CurrentWorkbook(){[Name="NameOfYourTable"]}[Content],* 
#"Changed Type" = Table.TransformColumnTypes(*Source*,{{"Dest", type text}, {"Bookweek", type date}})


----------



## ImkeF (Mar 16, 2016)

Almost:

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

So the filter arguments need their own "Cube.AttributeMemberId".



> I also changed the Bookweek type to Int64.Type . I don't know if this is correct.
> {"Bookweek", Int64.Type}})


Answer: Try it out - you will receive an error if it doesnt match the cube's. You should adjust your filter table's formats to the cube's field format for doing the filtering that should fold back to the server. If you need the cube-formats to be changed, do this after the big filtering is done because this might be performed locally and therefore stop query folding.


----------



## Kalle123 (Mar 15, 2016)

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 (Mar 16, 2016)

Hi!

Brilliant, thank you for the help with the filter and also about the information about the CurrentWorkbook-method! Really nice.

But now I get Token Comma Expected mark on the CallFunction instead. I tried to google CallFunction but I didn't get any wiser.. 


in
Filter,
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Dest", type text}, {"Bookweek", Int64.Type}})

*CallFunction = Table.AddColumn(#"Changed Type", "Custom", each Function([Bookweek], [Dest])*
in
CallFunction


----------



## ImkeF (Mar 16, 2016)

Sorry, my fault:



```
in
 Filter,
 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
 #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Dest", type text}, {"Bookweek", Int64.Type}}) [B],[/B]

CallFunction = Table.AddColumn(#"Changed Type", "Custom", each Function([Bookweek], [Dest])
 in
 CallFunction
```

Commas after each codeline, exept:
before the "in"
after the "in" itself


----------



## Kalle123 (Mar 16, 2016)

The never ending story. Now I get the error message at the last in. 


 in
 Filter,
 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
 #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Dest", type text}, {"Bookweek", Int64.Type}}) ,

CallFunction = Table.AddColumn(#"Changed Type", "Custom", each Function([Bookweek], [Dest])
* in*
 CallFunction


----------



## Kalle123 (Mar 16, 2016)

I don't know if this will help in any way. 

I tried to do just a function of it


```
let
    Query1 = let
Function = (Bookweek_, Dest_) =>

let
Source = AnalysisServices.Database("XXX", "XXX-Cubes", [TypedMeasureColumns=true]),
XXX = Source{[Id="XXX"]}[Data],
#"XXX Weekly Reports1" = XXX{[Id="XXX Weekly Reports"]}[Data],
 #"Added Items" = Cube.Transform(#"XXX 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 Cum TY", "[Measures].[CB1 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\XXX.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Bookweek", type text}, {"Dest", Int64.Type}})




in Function
in
    Query1
```

When i pressed Invoke I had to list Bookweek and DestCode (I wrote 201608 and ABC). I then got the following error message. 

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

What does that mean? Do I have anything wrong with the formatting?


----------



## Kalle123 (Mar 16, 2016)

Now I am starting to Spam! Sorry for that. I just sat and read the code again. Where do I specify that Bookweek_ And Dest_ are found in Table2? Just so I understand the logic behind it


----------



## ImkeF (Mar 16, 2016)

Try this code instead:


```
let
//    Query1 = let
Function = ([B]Bookweek_, Dest_) [/B]=>

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

#"XXX Weekly Reports1" = XXX{[Id="XXX Weekly Reports"]}[Data],
 #"Added Items" = Cube.Transform(#"XXX 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 Cum TY", "[Measures].[CB1 CUM TY]"}}),
Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&["&[B]Bookweek_[/B]&"]") and Cube.AttributeMemberId([Destination.Dest Code] = "[Destination].[Dest code].&["&[B]Dest_[/B]&"]")

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



//in Function
in
   // Query1
CallFunction
```

I commented out ("//") your steps in order for you to spot the difference. 
Main difference: The output in the last line: It has to be the last stepname of the query, as this is just a variable call (no closing bracket or sth similar). 

Not sure if I understand your last question correct. But you pass each rows content of your filter columns into the function call in step "CallFunction". They will be retrieved by the function in step "Function" (3rd row) and used in step "Filter".

M is partially lazy evaluating, so it will evaluate the function only once it is called.


----------



## Kalle123 (Mar 16, 2016)

So the never ending story continues. On the new code I get error message on the last sentence instead. 


```
in Function
in
 Query1
[B]CallFunction[/B]
```

If remove CallFunction I get a box saying Invoke and there is some text function (bookweek_ as any, Dest_ as any) as any. Which doesn't feel completly right. 

I really appriciate all the help you have given me! You are a star


----------



## ImkeF (Mar 16, 2016)

We'll get there - just a matter of time and patience 

Where are the // gone?
These are commenting out the code so they shouldn't be executed.
CallFunction is essential, but the part you've shown above should look like this: 

// in Function
in
// Query1
Call Function

.. thought they might help you understand what has been wrong with your previous code. But maybe this is easier then:
Just the code you need:


```
let

  Function = (Bookweek_, Dest_) =>

  let
    Source = AnalysisServices.Database("XXX", "XXX-Cubes", [TypedMeasureColumns=true]),
    XXX = Source{[Id="XXX"]}[Data],
    #"XXX Weekly Reports1" = XXX{[Id="XXX Weekly Reports"]}[Data],
    #"Added Items" = Cube.Transform(#"XXX 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 Cum TY", "[Measures].[CB1 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\XXX.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Bookweek", type text}, {"Dest", Int64.Type}}),
CallFunction = Table.AddColumn(#"Changed Type", "Custom", each Function([Bookweek], [Dest]))
in
CallFunction
```


----------



## Kalle123 (Mar 17, 2016)

Ahh! My mistake! I got confused.. Oh well, Now it looks like the query is okey but I get Error in the Custom column. It says:

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


----------



## ImkeF (Mar 17, 2016)

now you should hold on to your seat and take a deep breath 

Shifted one closing parenthesis in the filter step (from before "and" to after "([Destination.Dest Code]" :
   Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&["&Bookweek_&"]"and Cube.AttributeMemberId([Destination.Dest Code]*)* = "[Destination].[Dest code].&["&Dest_&"]")




```
let

  Function = (Bookweek_, Dest_) =>

  let
    Source = AnalysisServices.Database("XXX", "XXX-Cubes", [TypedMeasureColumns=true]),
    XXX = Source{[Id="XXX"]}[Data],
    #"XXX Weekly Reports1" = XXX{[Id="XXX Weekly Reports"]}[Data],
    #"Added Items" = Cube.Transform(#"XXX 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 Cum TY", "[Measures].[CB1 CUM TY]"}}),
   Filter = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([BookWeek.BookWeek]) = "[BookWeek].[BookWeek].&["&Bookweek_&"]" and Cube.AttributeMemberId([Destination.Dest Code][B])[/B] = "[Destination].[Dest code].&["&Dest_&"]")
  in
  Filter,

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


----------



## Kalle123 (Mar 15, 2016)

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 (Mar 17, 2016)

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 (Mar 17, 2016)

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 (Mar 18, 2016)

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 (Mar 18, 2016)

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.


----------

