Reference Excel cell in powerpivot sql query

K

Kalle123

New Member
Joined
May 5, 2015
Messages
24
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!
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sorry, my fault:


Code: 
 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
 
Upvote 0
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
 
Upvote 0
I don't know if this will help in any way.

I tried to do just a function of it

Code: 
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?
 
Last edited:
Upvote 0
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 :)
 
Upvote 0
Try this code instead:

Code: 
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.
 
Last edited:
Upvote 0
So the never ending story continues. On the new code I get error message on the last sentence instead.

Code: 
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 :)
 
Upvote 0
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:

Code: 
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
 
Upvote 0
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
 
Upvote 0
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_&"]")



Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Have SQL code refer to cells in worksheet
Replies
0
Views
796
All2Cheesy
A
SeanDamnit
  • Question Question
SQL and VBA weirdness
Replies
5
Views
1K
SeanDamnit
SeanDamnit
H
  • Question Question
Excel query based on cell value
Replies
1
Views
8K
Fazza
F
T
  • Question Question
Intermittent Errors Connecting Excel 2007 to SQL Server 2005
Replies
0
Views
639
tommill
T
R
  • Question Question
Assistance need with SQL query (Sit behind a combo button)
Replies
2
Views
821
VicRauch
VicRauch

Forum statistics

Threads
1,223,766
Messages
6,174,377
Members
452,560
Latest member
Turbos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top