MSPTDA 11: Power Query to Import from SQL Server Database in Excel or Power BI Desktop

Подписаться 1 млн

Просмотров 72 тыс.

50% 1

Download Excel START File: people.highline.edu/mgirvin/A...
Download Excel FINISHED File: people.highline.edu/mgirvin/A...
Download Power BI Desktop FINISHED File: people.highline.edu/mgirvin/A...
Download pdf Notes about Power Query: people.highline.edu/mgirvin/A...
Practice Problems: Assigned Homework:
Download homework file (Practice Problems) : people.highline.edu/mgirvin/A...
Example of Finished Homework: people.highline.edu/mgirvin/A...
link to Microsoft Notes on Direct Query: docs.microsoft.com/en-us/powe...
In this Video learn how to connect to an SQL Server Database and extract and transform data using Power Query in Excel and Power BI Desktop.
Topics:
1. (00:16) Introduction
2. (00:32) What is an SQL Server Database
3. (02:19) The Goal of our Queries and a look at the end result reports in Excel
4. (03:04) Comparing and Contrast using 1) Using Power Query User Interface or 2) Writing SQL Code in Power Query
5. (04:46) Example 1: Use Power Query User Interface to connect to SQL Server and Extract, Transform and Load Data.
6. (11:27) Example 2: Write SQL Code to connect to SQL Server and Extract, Transform and Load Data.
7. (14:44) Example 3: Using Power BI Desktop to connect to SQL Server and Import multiple Tables.
8. (18:29) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
The Power Query logo used in this video is copyright of and used with the express permission of powerquery.training
Thanks to Ken Puls and Miguel Escobar for letting me use their logo!!!!

Опубликовано:

9 сен 2018

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 176

@excelisfun Год назад

Hint for the Password: E x c e l I s F u n: E = Capital "E" x = lowercase "x" c = lowercase "c" e = lowercase "e" l = lowercase "l" = the letter after "k" I = Capital "I" = the letter after "h" s = lowercase "s" F = Capital "F" u = lowercase "u" n = lowercase "n" ! = punctuation = "!" = exclamation point

@satx9684 5 лет назад

Great video Mike. Watching your videos is how this whole Excel/Power BI journey started for me. I haven't been working with Excel very long but utilizing your resouces and others has already put me in a class above and beyond really anybody else within the companies I've worked for. Everything you do is much appreciated.

@excelisfun 5 лет назад

That is why I post!!!! I am so happy to hear that the resources that i post can help you advanced and have fun! Thank you for the support with your comment, Thumbs Ups and Sub : )

@1pkumara 4 года назад

Thanks thousand times dear Mike for clear explanation. Your support to expand our knowledge can not be valued by money. I wish and pray for your good health and wealth.

@patrickbanzon7145 6 месяцев назад

Thank you Mike I have started from E-Dab and now I am at this video, I am so grateful (I am from the Philippines education is not cheap here) for your videos If I ever meet you in person dinner is on me.

@excelisfun 6 месяцев назад

I am happy to help, and someday we'll have dinner : )

@JSUG4219 5 лет назад

Outstanding, as usual! Thanks, Mike for expanding all of our horizons!

@excelisfun 5 лет назад

You are welcome for the expanding fun with SQL connections, Jack! Thanks for your support : )

@Soulenergy31 5 лет назад

This is a 5 star video Mike, You are making History!!!!!! Thank you once again for such a great production and enlighting my self-learning curve!, God bless you!!!!!

@excelisfun 5 лет назад

You are welcome, Saul!!! Thanks for helping to support with your comments, Thumbs Up and Sub!!!!

@arteogr 5 лет назад

I do not know how to thank you, I have build a small database, thanks to your lessons, you have my 5 stars dish. Most valuable as always, something like a champion... Greatings from Crete.

@excelisfun 5 лет назад

You are welcome for the lessons, arteogr!!! Thank you for your support with your comment, Thumbs Up and Sub!!!

@ljubicar1987 4 года назад

I'm going to download SQL Server Management Studio right away and try to do this. So many things to learn! It is always fun watching your videos, thank you!

@excelisfun 4 года назад

You are welcome , sir : )

@excelisfun 5 лет назад

If you want to practice what you learned in this video, check out the practice problems (homework) provides in the links below the video : )

@excelisfun 4 года назад

You are right, no money, I just ask viwers like you pay each time you watch and learn with a comment and thumbs up? Simple, right ?

@alexjankowski6736 5 лет назад

Great video Mike. Thought it might have been a good idea for the video to point out the 'View Native Query' function in the UI to determine where query folding is enabled. MSPTDA is an awesome series. I look forward to more!

@excelisfun 5 лет назад

Wow!!!!! I did not know about this update. I just checked it out - it is amazing!!!!!!! Thank you so much for pointing this out to help me learn. I will have to show this cool new feature in an up and coning video : )

@andreasbeck3336 4 года назад

I just wanna say, thank you so much for all your hard work, Mike!!! And I just feel like saying it again, and again, and again =)

@excelisfun 4 года назад

Say thanks on every video that you learn from, and a thumbs up, and I am happy, Alexander : ) : )

@areekorn1 5 лет назад

Great work following from MSPTDA 1 to 11 now thanks mike

@MrBlereau 5 лет назад

Looking forward to the SQLisFun channel 😀

@tomr9969 5 лет назад

Great session! I have these down pretty well, PowerQuery, SQL, and PowerBI. Thanks again! Off to the next MSPTDA!

@excelisfun 5 лет назад

You are becoming a master!!!

@petermyran4986 5 лет назад

Done & Fun! I can hardly wait for the next one. Thanks Mike

@excelisfun 5 лет назад

Glad you like it, Peter! Next one is coming out today.

@nimrodzik1 5 лет назад

Another great video from the greatest Excel teacher ;)

@excelisfun 5 лет назад

Glad you like the video, nimrodzik1!!!! Thank you for your support : )

@himanshudalai1028 5 лет назад

A fantastic video & great learning. Thank you so much Mike for this awesome sharing !!

@excelisfun 5 лет назад

You are welcome for the sharing, HIMANSHU!!!!

@shoeshines2121 4 года назад

Echoing a comment below, but I'm also ready for the SQLisFun channel :D Thank you for putting this content together.

@MalinaC 5 лет назад

Another cool video about Power Query. Thank you so much for sharing you enormous knowledge in such an easy way!

@excelisfun 5 лет назад

You are welcome, Teammate! Thanks for your support : )

@MalinaC 5 лет назад

My pleasure :)

@orcawilly54 4 года назад

Thank you for your great video and very detailed explanation.

@chrism9037 5 лет назад

Incredible Mike!! Another great one..

@excelisfun 5 лет назад

Glad it is incredible for you, Chris! I have a part two coming out today that will add a really great new PQ feature for SQL : ) Thanks for the support, as always, Chris!!!

@mohammedmohsinabbas2656 2 года назад

Thank you so much for those great lessons each time I have hard time to solve something I find the best way to do it thanks to you

@excelisfun 2 года назад

You are welcome!

@marioszervas1245 8 месяцев назад

Great explanation my friend

@filipsvakjaroski20 3 года назад

Thank you Mike :)

@fabryespejo2697 5 лет назад

Impresionante video!! Excelente explicacion!! Saludos!!

@excelisfun 5 лет назад

De nada, fabry! Gracias por el apoyo : ) You are welcome, fabry!!! Thank you for the support : )

@zm2813 5 лет назад

Outstanding as always!

@excelisfun 5 лет назад

Glad it is outstanding for you, Zak M!!!!

@davebowman5392 5 лет назад

What a great series of tutorials this is, I know people who are scared to death of or simply refuse to use any of the 'power' tools, they're missing so much.

@excelisfun 5 лет назад

You are correct about that, Dave. For whatever reason, the Power Tools are just not used as much by people who would love them if they tried : ) As always, Dave, Thanks for your support!

@GeertDelmulle 5 лет назад

Dave Bowman That’s a good observation right there. And I was the same, being a regular excel user. But not anymore: in PQ you can do stuff that is either impossible in regular Excel (unpivot anyone?) or would require some crazy array formula that only Mike could come up with. Now PQ is my go to power tool for data transformation and cleaning. And I learned it all here on the Absolute Best Excel learning YT channel, bar none! Now people laugh when I mention ExcelisFun, but look a little dazed when they see what I can do (so far) - real life applications in my professional environment, too. That’s true power, right there. Thank you, Mike!

@excelisfun 5 лет назад

You are welcome, Geert! And I agree with you Power Query is just so amazing in so many ways. I know of no other tool that can simultaneously connect to so many data sources, then clean and transform, then import. Thanks for the support, Geert : )

@SyedMuzammilMahasanShahi 5 лет назад

Thanks Mike for this EXCELlent Fun video.

@excelisfun 5 лет назад

You are welcome, Syed! Thanks fro your awesome support : )

@userme2803 2 года назад

excellent video thank you

@papeleria.4229 3 года назад

For me, it's easier to write the SQL code rather than using the Power Quey interface, great video

@ismailismaili0071 5 лет назад

you are always the best Mr. Mike

@excelisfun 5 лет назад

Thank you so much for your awesome and consistent support, Ismail Ismaili!!!!!

@ismailismaili0071 5 лет назад

@@excelisfun as far as I'm alive never stop benefitting from your video thank uou Mr Mike

@excelisfun 5 лет назад

: )

@cmaman1 4 года назад

your videos become an addiction for me, but are good addiction one for sure :)

@excelisfun 4 года назад

Yes, a good one ; )

@jazzista1967 5 лет назад

Excellent Video Mike. I was hoping that you would open the SQL Management Studio and write the SQL statement . Look forward to the next videos. Thanks for all your insights

@excelisfun 5 лет назад

You are welcome, jazzista!!! Thanks for your support : )

@amerfarooq1142 3 года назад

so amazing information sir. thanks for each & every informative video

@excelisfun 3 года назад

Glad it helps, Amer!!

@armondnazarian4455 4 года назад

clearly explained!!

@excelisfun 4 года назад

Glad it is clear, Armond!!!! : )

@AsifAli-ws5gg 3 года назад

Thank you Mike

@excelisfun 3 года назад

You are welcome!

@johnborg5419 5 лет назад

Thanks Mike. Very Interesting. :)

@excelisfun 5 лет назад

You are welcome, John Borg!!!! Thanks for your support : )

@mattschoular8844 5 лет назад

Awesome. Thanks Mike....

@excelisfun 5 лет назад

You are welcome, Matt!!! Thank you for your support : )

@monjurrahi167 4 года назад

Very helpful.

@excelisfun 4 года назад

Glad it helps!!!

@rajkumarpalle8217 2 года назад

Thank you for sharing good information,....I was wondering how to get the data with help of sql quires, into the power query. Now i got clarified. Thank you very much.

@shubhampawar8506 2 года назад

You are superb 💯❤️👌

@excelisfun 2 года назад

SQL fun : )

@ghahrepascale3256 Год назад

This free content is a gem on YT. Thank you for all your hard work. Quick question, is it common for the server to go down? I've been having trouble accessing the SQL Database.

@excelisfun Год назад

The sever is not down. Problems in accessing this is either your network or typing error.

@Sandeepkol3 5 лет назад

Great tutorial

@excelisfun 5 лет назад

Glad you like it, Bhola! Thanks for the support with your comment, Thumbs Up and Sub : )

@henrytnh1 4 года назад

Thank you

@tonandaya7414 3 года назад

This is fantastic Mike ! Amazing ! An eye opener for me that would make me more efficient at work. I was trying to download the excel file, but it says forbidden, i don't have access anymore. Previously i was able to download the files and follow your teaching, but now cannot, not sure what happened.

@danielwerner9543 2 года назад

Thanks!

@excelisfun 2 года назад

Thanks for your kind donation, Daniel!!! It helps me to keep making videos : ) : )

@kamranb1369 5 лет назад

Nice one Mike :-)

@excelisfun 5 лет назад

Glad it was nice for you, K B!!!! Thanks for your support : )

@vitormicillo1 4 года назад

Hi I just found this videos, really good, I have a question for you. If I need create the inverse way and using the power Bi or the power query, after using the design model and create the query I need view the "raw" like select(x,y,z)... query and run in the sql directly, it is possivel?

@m.sz.120 5 лет назад

Thank you for the next instalment in this series, Mike. Yes, the finished files are somehow corrupt/missing. Thank you for looking into it later.

@excelisfun 5 лет назад

You are welcome! I have fixed the file links so I hope they work now. Thank you for helping with errors like this : ) Go Team!!!

@m.sz.120 5 лет назад

It is all good now. Thanks again.

@excelisfun 5 лет назад

Great!

@excelisfun 5 лет назад

The Power Query logo used in this video is copyright of and used with the express permission of powerquery.training Thanks to Ken Puls and Miguel Escobar for letting me use their logo!!!!

@PRIYANKASharma-uv8lx Год назад

Please make a video on real life projects also how to submit them on GitHub. your teaching is helpful. Thanks

@ogwalfrancis 5 лет назад

This is an amazing video, i want to inquire how to copy or export visualisation from power BI to word document and to excel,

@AsifAli-ws5gg 3 года назад

I dont know why google did not recommend me any video of this channel.

@excelisfun 3 года назад

Google is a powerful monopoly that does not try to make the world a better place. Google does not care about where the the videos or links they provide have good content.

@marshal115 5 лет назад

Great video! Happened to find you’ve already been using Excel 2019. Anything new on PQ?

@excelisfun 5 лет назад

I am not using Excel 2019, I am using Office 365. Office 365 is THE only version to have because it will get monthly updates with new features. As for new things, there are a few. For example, the video i post later today will a great new Power Query feature.

@tiktok4372 5 лет назад

Thank you for great video, but can u conclude which way is better to get data from SQL database? Because English is not my native language, I don’t understand u at some point 😢. But I’m still following this course 😊

@ingrubenssa 5 лет назад

Hi How can I pass parameters to the SQL sentence (query) from a cell (For example a picker date)?

@lazarjoksimovic 3 года назад

Is there any limit how many tables we can import from sql server to excel query or desktop BI , because I have in Navision big date base, excel or BI loads 10 000 tables and I can't see tables that i need, but I can see all tables in date base throw Microsoft SQL Management Studio? Thx :)

@nathan451 5 лет назад

Hello Mike, thank you so much for your brilliant videos. It’s so helpful. Just a question: could there be a performance difference between the 2 methods (power query UI vs SQL code)? Thanks again for all you give to the XL/BI community. And greetings from Paris. Nathan.

@excelisfun 5 лет назад

Yes, the tool, Power Query, is built for us to use Power Query UI, and usually that is the most efficient. But for some SQL ninjas, they want to do it that way. I just had to show one example of SQL.

@nathan451 5 лет назад

ExcelIsFun Thank you.

@dheslop1 5 лет назад

Mike, thanks, this is fantastic. Wanted to say thanks as the last few months your guidance in excel and power query has helped so much. One thing I noticed in this video was the "include relationship columns" which looks amazing. Should this work exactly the same if my source is an Oracle database rather than a SQL database? I cannot seem to get this to identify related columns even though I know they exist between my source tables. Apologies if you've covered this elsewhere! Thanks David

@excelisfun 5 лет назад

I have not connected to an Oracle database. I thought that it would show related columns... Are you sure that there a relationship exists? I know that in a Microsoft SQL database, if there is defined relationship, this feature does not work.

@dheslop1 5 лет назад

ExcelIsFun to be absolutely honest, I don't think I understand enough about relationships or the database set up to say one way or the other. I can certainly see that for example a sales account code in the sales transaction table is a primary key to return say the sales account name in the customers table. Now you mention it I would assume that power query must rely on the database to identify related tables? The source has around 1200 tables / views so how could power query review them all and identify relationships.

@excelisfun 5 лет назад

Yes, for all databases that I have connected to, a relationship must be defined, regardless if there is a primary key or not. You do need it though, you can import two tables and do a merge if there is no relationship. I have a few videos about this: here is one: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8F7v6YvnsiY.html

@dheslop1 5 лет назад

ExcelIsFun thanks for your response Mike, I'm not entirely sure how to check out whether there are defined relationships? The merge function works very well, but I was hoping related tables would help where I am struggling to identify where some of the data is held (as I said there are so many tables and names are poor). Program developers not being helpful as it would mean we could write our own reports instead of buying additional developer time.

@dunnobe Год назад

I'm getting this error when trying to connect to the db: Details: "Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=719; handshake=29806;

@hosseinhosseinpoor9561 2 года назад

good

@user-ri8tc2ru9h 4 года назад

If i want to utilize parameter table for the options of server and sql statement description, how to link the table? I tried to use the same way of the parameter table to link folder path(folder.files -> odbc.query (“dsn=server name”, “sql statement reference query connection”), but I fail for this way.. Could you help me how to solve this issue so that I can easily select odbc server and modify sql statement from the excel work sheet (not by query edit window)?

@raitup00 3 года назад

Now I want to learn SQL with you as a teacher. Let us know when SQLIsFun YT Channel be ready...

@excelisfun 3 года назад

Thanks for the kind words, but I am not good enough with SQL to teach, yet ; )

@mohamedamr8081 3 года назад

Hey Michael thanks for sharing. I've been trying to connect to that database for a few weeks and I keep getting: The name provided is not a properly formed account name. I tried pinging and it seems like the aws is down or something, also https:\\pond.highline.edu doesn't work.

@barttitulaerexcelbart9400 5 лет назад

Thanks Mike, I have seen/heard the term "Direct Query" so often but this is the first time I really understand. You can learn Excel and Power BI with books and internet but for SQL it is a bit more difficult. Thanks for presenting this so clearly. One question: in the SQL example you did not enter credentials why? Because you did this in the first part?

@excelisfun 5 лет назад

Glad it all helps, Bart! Yes, we were connected to database and so we did not need it the second time. Thanks for your support, as always : )

@excelisfun 5 лет назад

BTW, you can try Direct Query on this database and then make a simple visualization with a slicer, then change the criteria, and watch how slow it is.

@barttitulaerexcelbart9400 5 лет назад

I did the homework assignment: it works the other way around as well.

@excelisfun 5 лет назад

Yes, indeed! Cool! Isn't it fun to have homework for a RU-vid Video?

@atulsrivastava2790 3 года назад

Hi, is it possible to reference a cell in the excel file in the SQL query?

@zhanglei3874 4 года назад

I cannot connect to the SQL which shows: Details: "Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context."

@superfreiheit1 5 лет назад

If a do a JOIN with PowerQuery with this data, then all take long time to load. Is this normal?

@chavelooo33 4 года назад

Hi ExcelIsFun, definitely you are the best. I have been trying to find something similar to your channel but with sql. Any recommendation for a beginner level? I am willing to accept youtube Channels or books., Additionally I want to send you a private message with a query but I do not know where. I do not mind to pay for your services as consultant. thank you.

@DIGITAL_COOKING 5 лет назад

please question mike, we know that creating a measure with DAX is better than column whenever it's possible but what about creating a column with power query compare to : 1) creating a column with DAX ( because I have an idea that with power query is better ) 2) creating a measure with DAX do the same calculation with a column created with power query hope you understand my question beside the question, you did very well video mike, and for me, I learn a lot of stuff in your channel believe me, I was lost before and didn't even know where to start and how to finish....now i found my path and know very well how to study efficientely

@excelisfun 5 лет назад

1) Well, this is not always true: "we know that creating a measure with DAX is better than column whenever it's possible". There are all sorts of decision points that go into deciding whether or not to use a Measure or Calculated Column. But in general, it is true. 2) Power Query Custom Column or DAX Calculated Column? I have not tested on large enough data sets to really see a difference, but conceptually, both will have to be recalculated only when you refresh the data set. Because of this, there might be some advantage to using Power Query because there is the opportunity to send the query back to an SQL database, if you are connected in that way. 3) If we do use a Custom Column in Power Query, it is only calculated when the data set is refreshed. So when the Data is loaded to the Data Model, it is just data, like sales numbers, and so a DAX Measure just works on it normally - meaning, it does not ask Power Query to refresh the Custom Column in Power Query.

@excelisfun 5 лет назад

Thanks you very much for your kind words, and I am glad that you are less lost and are learning well at this excelisfun channel. Be sure to tell all your friends, share the posts, post cool stuff on linked-in and such - to help spread the word : )

@DIGITAL_COOKING 5 лет назад

got it

@pollora6233 3 года назад

Hello, are the Credentials to access SQL Server Database still working? Thanks again for this amazing material

@excelisfun 3 года назад

Yes they are

@CelebrintaR 5 лет назад

Y si la BBDD SQL está en un entorno remoto y no podemos conectarla por nombre? ¿Cómo conozco la IP o consigo que lo sepa por el nombre? ¿Cómo configuro esto para todos los usuarios de OneDrive de la empresa que disponen en sus espacios OneDrive Excel que conectan con un SQL Server?

@ladibells 2 года назад

Hi Mike, I recently came across your channel and they are a great help but i can't download the practice files. I tried visiting your site but it's down. Kindly help fix this sir

@excelisfun 2 года назад

It seems to be working. Sometimes the server is down.

@nirajegath8911 4 года назад

is the server still accessible? Tried connecting but getting the error: " Microsoft SQL: A conection was established with the server , but then an error occurred during the pre-login handshake. (provider:TCP Provider,error:0 - The specified network name is no longer available.)". Thank you.

@excelisfun 4 года назад

Serever is working. Server: pond.highline.edu Database: boomerang User: excelisfun Password: ExcelIsFun! almost certainly the password you typed was not correct : (

@pedrokobler6555 Год назад

Unfortunately I was not able to access the data server.

@jeffkasavan93 4 года назад

Is the server still online? I get this error: DataSource.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Details: DataSourceKind=SQL DataSourcePath=pond.highline.edu;boomerang Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Number=53 Class=20

@ljubicar1987 4 года назад

Great video, as always! Thanks for sharing! I have to share something that troubles me - I have been studying Power Query and M Code a lot lately, and on every job interview they ask for knowledge of SQL. And as you said in the video "Unless you are SQL ninja, you don't have to write code!", so why should I bother learning it!? So am thinking to myself - maybe they aren't even aware of how Powerful PQ is. Somebody, please correct me if I am wrong.

@excelisfun 4 года назад

Yes, it is correct that most are not aware of the power of M Code and Power Query and Power BI. However, SQL is still pretty common, and you should probably learn the basics. If we always work in Excel, Power Pivot, Power Query and Power BI, we do not need to use SQL. I always work in Excel and Power Query and Power BI, so I do not need to know SQL, however, I did learn it just to know and it does help, since many of the concepts in Power Query and SQL based, even though M Code is its very own unique language. Lastly, very few people in the world are really good with M Code at this point. And becasue it is so powerful if you work in an entity that uses all the Microsoft stuff and you know M Code, you can be a huge hero : ) : ) : )

@ljubicar1987 4 года назад

@@excelisfun Thank you for your opinion, it means a lot to me! I do know the basics of SQL and Access, and it helped me to understand the concepts of relationships and database, but I think this basic knowledge is enough for doing reporting in Excel and Power BI. However, I will continue to learn new stuff, and maybe someday I master SQL too.😊 👩‍💻

@mishalqamar7302 9 месяцев назад

@@ljubicar1987did you learn sql?

@ljubicar1987 9 месяцев назад

@@mishalqamar7302 yes, I had to learn eventually, and I don't regret it.🙂

@user-ju1it8wf2j 4 года назад

I have my database in MariaDB and I had connected the database through ODBC and when I had selected the database into Power Query, I got all the tables listed. (I had attached a link to the screenshot) Each table is having different structure and as of now I had created 4 queries to extract acmaster, invmaster, invoice1, prodmaster and then made the relationships. Is there a way to expand the tables in different queries? In other words, can we generate multiple query results from single MCode? drive.google.com/open?id=1k8JqslTVL-jyAkuXvubQ-HrZu2ykhc-D

@excelisfun 4 года назад

If I understand your question, then import table as a query, then refer to that query by name in a second query. On the left in the queries pane, you can right-click a query and click on reference or duplicate. Reference tells the query to use the first query, and if the first query changes, the second query referencing it will see the changes. Duplicating a query is what you do when you want to copy the code from the first query and not have the new query change when the first query changes.

@user-ju1it8wf2j 4 года назад

@@excelisfun Wow man! How did I not even think about it! I have tried as you told! Named the query as TablesList and used = TablesList{[Name="acmaster"]}[Data] to get the acmaster table.

@thriftymillionaire5968 5 лет назад

Please do you know how to formulate the following condition in excel; when you have two cells one with high and the other with low value and one of the cell is colored green. so you want to return a value of '1' if the lowest value amoung the two cell is green color and '0' is the highest value amoung the cell is green. is there a condition or formula for the statement in excel

@excelisfun 5 лет назад

You need VBA for that. I am not good with VBA. Try this great Excel Question site for posting a question: mrexcel.com/forum

@ikar2k 5 лет назад

Hi Mike! How about using the macro functions in that case? For example (func_type №38): "Excel Magic Trick 1355 Extract Data at each Bold Font, Display Horizontally: GET.CELL Macro Function" ;) And thx to you a million!

@aruchan123 5 лет назад

Hi Mike, Thanks for your wonderful videos. As always, your work inspire me a lot while processing a lot of data set and reports. Thanks and really appreciate your works!! I've tried to access the DB but got "Unable to Connect" error "Details:MicorSoft SQL: The target principal name is incorrect. Cannot generate SSPI Context. I did use "pond.highline.edu" and "boomerang" DB. Is there any restriction to access it? Thanks !!

@excelisfun 5 лет назад

There is no restrictions. Did you try the old "turn off everything and start again" trick? The way you spelled it in your comment appears correct. I am not sure : ( Can you try again and post back?

@aruchan123 5 лет назад

Yes. Now I can download. Look likes just at server glitch at the time I was downloading. Once again, thanks for your marvelous videos and study docs !!

@excelisfun 5 лет назад

Glad you can download it now : )

@excelisfun 5 лет назад

Thanks for your support, aru aru, with your comments, Thumbs Up, Sub and tell all your friends : )

@GeertDelmulle 5 лет назад

Mike, some files can not be downloaded: - "Excel FINISHED File" => Page Not Found error - "Example of Finished Homework" => actually yields the Start File Can you please fix this? Thanks!

@excelisfun 5 лет назад

Sorry about that. It is so crazy how many 100s of details go into producing a video and files like this... So it is often the case that I makes some mistakes - that is why it is so great to be on such a great Online Excel Team and have teammates like you to help point out the errors and corrections : ) I fixed the links (I hope). All the files should be working.

@GeertDelmulle 5 лет назад

Yep, everything is A-OK, now. Great & thanks!

@GeertDelmulle 5 лет назад

BTW: I don't call them "errors and corrections", I just call them: "opportunities for further improvement and perfection" ;-)

@excelisfun 5 лет назад

Yes!!!!

@excelisfun 5 лет назад

That is great! Similar to you, Geert, in the classes that I teach at Highline College, I call tests: opportunities : )

@navjotsingh2251 2 года назад

Now we know how to import, how do we export from power query back to SQL!?

@anisdadani8980 4 года назад

Could not access the SQL database hence unable to classwork and homework

@excelisfun 4 года назад

What message do you get when you try to connect?

@excelisfun 4 года назад

Can you try it again now, it should be working.

@ExceliAdam 5 лет назад

Connection to the database that someone created is simple :D Now I'm trying to create a database like yours so that I can connect to it and it turns out to be difficult for me :(

@excelisfun 5 лет назад

Yes, this is not a class about creating databases, just how to connect and make data model and reporting and dashboard magic!!!! Thanks for the support, Excel.i Adam!

@NoShadowOfDoubt1 5 лет назад

Someone did a speed test and found power bi to be six times quicker than power query....

@excelisfun 5 лет назад

What do you mean? Power Query is part of Power BI. What test? Almost all Power BI Solutions use Power Query. I do not follow.

@NoShadowOfDoubt1 5 лет назад

I found his test to be very true, pulled the same information with multiple tables from SQL database...power query in Excel takes several minutes to finish refreshing where Power BI finishes in less than a minute, you have also mentioned that power query uses a different code than DAX...

@excelisfun 5 лет назад

No, what I said in other videos, and what is factually true, is that Excel Power Pivot generated PivotTables must send the DAX formula request using MDX, as compared to the internal DAX request that the Power BI Desktop engine uses. This makes Power BI faster for DAX calculations. In essence, in Excel only one processor is allowed to used at a time, but in Power BI Desktop multiple processors can be used. But this has noting to do what we did in this video or in any of the Power Query Videos so far in this class. I have never heard or read in any source that Power Query and M Code works differently in Excel and Power BI Desktop. DAX runs differently, though.