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 … 🙂
Dick
dick@plogic.ca
p.s. go HERE … 🙂
Dick Moffat's Spreadsheet and BI Blog 
Isn’t it easier to use and change power queries these days?
Go ahead … 😉
Yep, with Excel 2016 I can even use VBA to change the source line of my query …
“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?
@ Giorgio
Dim q as WorkbookQuery
For each q in ActiveWorkbook.Queries
Debug.Print q.Name
Debug.Print q.Formula
Next
q.Formula is just a string … you can replace substrings, you can split it at line breaks and so on …
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?
@ 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”
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?
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 ….
“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?
Aaagh again .. Yes !! Will fix … I changed the name to make more sense then didn’t change my post … Will fix right now ..
3 fields … Sql, server and database
Thanks Dick, why do you need to use Table.TransformColumnTypes?
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?
Dick
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?
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 :-).
Dick
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 …..
This last message of yours together with https://dmoffat.wordpress.com/2013/08/29/access-devs-for-powerpivot/ should hearten all Access aficionados
“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 ..
Dick