How To Change Connection Type In Power Pivot

Every day, day in and day out, the most popular posting I have here is this one:

and this one:

I personally use these techniques all the time, for years now and it is my standard modus-operandi for Power Pivot development in Excel.

Yet despite the fact that everyone comes here every day and looks at this page, I HAVE NEVER heard anything back from anyone whether they actually use this, if it works for them or not .. Not once…

That’s pretty frustrating people … 🙂


p.s. go HERE … 🙂

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Excel, Excel 2013, Office Automation, OLAP, Power BI, Power BI Desktop, PowerPivot, Process Automation, Spreadsheets, Uncategorized. Bookmark the permalink.

19 Responses to How To Change Connection Type In Power Pivot

  1. FrankT says:

    Isn’t it easier to use and change power queries these days?

  2. Giorgio says:

    “with Excel 2016 I can even use VBA to change the source line of my query ”
    Can you give an example of VBA code that does that?

    • FrankT says:

      @ Giorgio

      Dim q as WorkbookQuery
      For each q in ActiveWorkbook.Queries
      Debug.Print q.Name
      Debug.Print q.Formula

      q.Formula is just a string … you can replace substrings, you can split it at line breaks and so on …

  3. Giorgio says:

    Thank you Frank, so to change the source line do you set q.Formula = whichever string ?
    If so, could you give an example of such string? Is it an SQL expression?

    • FrankT says:

      @ Giorgio

      Connect to a data source (maybe SQL Server) in Power Query.
      In the “Advanced Editor” you can see the respective “M code”.
      q.Formula is exactly this “M code”

      BTW, Dick has just written about it:
      “How To Change Data Sources Dynamically In Excel With Power Query”

      • Giorgio says:

        Thank you Frank, so, using Dick’s post as example, I suppose the M code should be Sql.Database(qryMasterTable[Server]{0},qryMasterTable[Database]{0}, [Query=qryMasterTable[SQL]{0}])
        but what is qryMasterTable? I don’t see it mentioned anywhere else in Dick’s post.
        And what is the procedure to connect to a data source in Power Query?

      • Biggus Dickus says:

        Oops !! 😦 i mistakenly called it “tblMasterTable” in one poace and “qryMasterTable” in another … Sorry … I have changed it … You need to name the first query “qryMasterTable” for the code in the second query to function ….

  4. Giorgio says:

    “make it a table and name it “tblConnections”
    “This should import the data from the SQL that you entered from the Server and Database you put in the tblSQL table.”
    Are tblConnections and tblSQL actually the same table? And how many fields does this table have?

    • Biggus Dickus says:

      Aaagh again .. Yes !! Will fix … I changed the name to make more sense then didn’t change my post … Will fix right now ..

    • Biggus Dickus says:

      3 fields … Sql, server and database

      • Giorgio says:

        Thanks Dick, why do you need to use Table.TransformColumnTypes?

      • Biggus Dickus says:

        That sucks but I kept getting a conversion error on a SQL statement that included numbers inside the text like

        select * from fnGetdata((’01/01/201′,’12/31/2012′)

        …. So I had to set the value of the fields to text this way …

        If not for that this would be painfully simple I think…

        Did this work for you?


  5. Giorgio says:

    I’m just getting my head round M Code and Power Query, I work mostly with Access and trying to test the viability of integration with Excel’s new(or improved?) capabilities such as Power Pivot, Power Query and Power BI and in the case of Power Queries, what have they got to offer more than Access?

    • Biggus Dickus says:

      BIG question eh?

      I’ve been doing data-driven spreadsheets since 1985 and Access since V1 … The truth is a combo of all but that’s too much for everyone .. Then there’s those out there who think their thing (PowerPivot, PowerQuery, PowerBI, MDC and DAx, Access, Ssas ) is the only thing and they’re misleading everyone for their own ends. I try to promote that it’s all a blend of tech and skills in Excel itself (for example) buy no one wants to hear that anymore – especially not Microsoft.

      It’s just too big for everyone… While I have done it all for 30+ years so I’m just too old to be taken seriously.

      I really shouldn’t blog anymore when I get attacked by people who believe that their “thing” is going to cure cancer and bring peace to the middle east – and it’s all bullshit but they have more testosterone than I do .. It’s a sad scenario.. But if I’m not visible then I am not visible .. So I will keep blogging . I am now moderatong all comments so I can control the message from now on ..

      Thanx for your thoughts btw :-).


      • Biggus Dickus says:

        One more thing … I have worked hard to come up with realistic, practical and reliable best practices that allow for solutions that endure and are maintainable .. Yet there is always a movement to try to make everything as complicated as possible … Something that I see as self-serving from the traditional geeky nerds who have always been in this industry… They get chubby about how smart it makes them feel and they don’t have anything else in theor lives to feel good about I guess.

        So my approach is not really appreciated even though it is the only way anyone will ever get anything out of all this tech that MS is puking out day after day 😉 ..

        Oh well …..

      • Giorgio says:

        This last message of yours together with should hearten all Access aficionados

      • Biggus Dickus says:

        “Should” hearten them but I got zero response to that until you did just now 😦 …

        Once again everyone stays in one “bucket” and are missing out on the fact that it’s all there IF you mix Access and Excel with Power Pivot and Power Query/Get and Transform and now maybe even Power BI with SQL Server and/or SQL Azure … It’s all there …

        Sadly I even find that clients either think of me as an Access “guy” or and Excel “guy” and can’t see that I’m actually both and that this is to their benefit .. Oh well ..


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s