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!

 

[ImkeF]

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]

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

 

[Kalle123]

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]

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]

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]

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]

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]

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]

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.