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!

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]

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]

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]

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]

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]

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]

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]

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]

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]

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