Home Forums Power Pivot SQL in PowerPivot: using excel data to make dynamic query

This topic contains 3 replies, has 2 voices, and was last updated by  Andre 5 days, 12 hours ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #11313

    mathilde
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hi,

    I’m coming back to powerpivot after a few years away, so probably a newbie question. I’m writing a simple sql query against a very large accounting database (too big for excel). I’d like my users to be able to fix one of the query parameters themselves (without having to edit the sql code), so that the sql query only retrieves the data they need. Can’t use a slicer, because this would imply loading the full data table in their model, the filter needs to happen before that (or at least, it’s my understanding!).

    My idea was to create a very simple data table into the excel/powerpivot model, with the (manually adjusted) list of values they wanted to be considered in their report.  The sql code would look like the example below this message. In short, it’s just trying to do this :

    SELECT * FROM [BigDataTable] WHERE [BigDataTable].[Field] = ‘dynamic value entered manually in an excel cell of the model by the user’ which I’ve expanded with [BigDataTable].[Field] IN (Nested SQL Query based on a data table in the excel file) – this way the user could pick multiple values for this parameter, but this isn’t critical, just being able to pick 1 would be awesome! (the table in excel has two columns : “type” with the parameters’ type, “value” with the accepted parameters, and can only have one line if they only want one value to be retrieved)

    I don’t know how to refer to a cell value within the excel workbook in the sql used to create the powerpivot data table. The nested “select” statement in the example below doesn’t work (I’ve tried with full workbook path as well, in case tab name wasn’t enough).

    I’ve been searching online for a while now, and must be lacking the right keywords for my questions, or what I’m trying to achieve is impossible /  should be done completely differently? Please feel free to refer me to the right post in this forum if it’s been covered, or to let me know if my question is stupid because there’s another way.

    Thank you so much in for your attention,

    Mathilde
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>SELECT</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME].[G_L Account No_] as GL_Account,</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME].[Global Dimension 1 Code] as Contract_Code,</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME].[Global Dimension 2 Code] as Country_Code,</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME].[Posting Date] as Date,</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME].[Debit Amount] as Debit,</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME].[Credit Amount] as Credit</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>FROM</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME]</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>WHERE</p>
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt;”>[DATABASENAME].[Global Dimension 1 Code] in (SELECT [value] FROM [‘ExcelTabName$’] WHERE [type]=’contract’);</p>

    #11985

    Andre
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6
    #12011

    mathilde
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Thanks a lot for your feedback!

    It took me a while to figure it out, but the link above made me change my strategy : I was approaching this wrong by trying to build the data model in PowerPivot, while I probably need to create it in PowerQuery first (I also found this post which helped me get the difference between the two: https://powerpivotpro.com/2016/03/power-pivot-versus-power-query-power-bi-dilemma/).

    Now, using the tip in the suggested link (https://stackoverflow.com/questions/26989279/how-can-i-reference-a-cells-value-in-a-power-query), I’m facing a new issue : how to call a user-defined function within the Query inside the Sql.Database statement? I started some online trainings on power Query M language, but got nowhere near this (I’m used to VBA, and a heavy Excel user, but that’s about it!)

    Using the custom function suggested in this post (GetValue() ), and a named range in Excel (“MyRange”, single cell with text) I’m trying to do something like this :
    <p style=”margin: 0in; font-family: Calibri; font-size: 11.0pt; color: #7f7f7f;”>= Sql.Database(“server”, “database”, [Query=”SELECT#(lf)[table].[fieldname1] as Field1,#(lf)FROM #(lf)[table]#(lf)WHERE#(lf)[table].[Field2]=Excel.CurrentWorkbook(){[Name=’MyRange’]}[Content]{0}[Column1]”])</p>
    I can see something is wrong with this line : mixing some power Query M commands inside a text which is itself a parameter (sql command) for the sql.database() function, but really don’t know where to start to fix it… (and I’m not even using the user-defined function, just some piece of it, so it doesn’t even feel like a good attempt!).

    Again, maybe my approach is all wrong and I’m not using the right tools to solve my original question (like in the first post!), would be very grateful for any advice, online resource or tip to point me in the right direction!

    Cheers,

    Mathilde

     

    #12020

    Andre
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    I created an example. Cell G2 contains my selection I named the cell “SelectedID”. I created a query to my sql server and saved it. In the advanced editor the query is:

    let
    Bron = Sql.Database(“DESKTOP-G1FPFGO”, “17012018”, [Query=”select * from dataset”, HierarchicalNavigation=true])
    in
    Bron

    The value of the selected id is retrieved with the line:
    id= Excel.CurrentWorkbook(){[Name=”SelectedID”]}[Content]{0}[Column1]

    Next this id has to be incorporated into the sql statement:
    Query=”select * from dataset where id= “&Number.ToText(id)&””

    The Number.ToText is needed to convert the integer value to a string so it can be used with the &.

    So the query becomes:

    let
    id= Excel.CurrentWorkbook(){[Name=”SelectedID”]}[Content]{0}[Column1],
    Bron = Sql.Database(“DESKTOP-G1FPFGO”, “17012018”, [Query=”select * from dataset where id= “&Number.ToText(id)&””, HierarchicalNavigation=true])
    in
    Bron

    Attachments:
    You must be logged in to view attached files.
Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.