Brilliant video. I'm an advanced Excel user and I finally decided to explore this - I'll start to use it immediately!
@MikeThomas67
7 ай бұрын
@chasmoore1746 Thank you and glad I could help
@ChinWagVentureGroup
Жыл бұрын
Excellent video - Many thanks and easy to follow
@MikeThomas67
Жыл бұрын
Glad it helped
@rraymond8988
Жыл бұрын
This was very helpful, thank you. As someone just starting to learn Power Query for mac, I appreciated your explanations along the way as it helps develop more of an intuitiveness with using the function that I was not getting watching some other videos online.
@MikeThomas67
Жыл бұрын
You're very welcome!
@annajennison-phillips9773
Жыл бұрын
Thanks so much Mike - it's really good to be taken through the Mac PQ functions in a nice straightforward way. I was previously getting really frustrated trying to understand PQ from videos where the presenter is on Windows, and half the functions are missing or slightly different when you try to find it in Mac. So so great - I'm a very happy learner today!
@MikeThomas67
Жыл бұрын
@anna Glad to know you're a happy learner!
@dee01_
Жыл бұрын
This exactly was my issue, hope I see what I’m looking for here.
@charlieosborne9615
2 жыл бұрын
As a Mac user and semi-occasional Excel user, I've not needed to user Power Query before, in fact I didn't even know what it was but I can really see the benefits. Good on you Microsoft and thank you Mike for this session. Really well presented and packed full of information
@MikeThomas67
2 жыл бұрын
Thanks Charlie. Hope it converts you from a semi-occasional Excel user to a more-than-semi-occasional Excel user :-)
@GG-ut3es
2 ай бұрын
Great tutorial! Can you load just the merge query on MAC? When I hit close and load it does all of the sheets. Thanks!
@liushunan4310
Жыл бұрын
Possible to share the file you are using during the tutorial because it is easy for us to practise during the training,many thanks!
@isabelaprecci8244
Жыл бұрын
Is the file you've used available to download?
@mtheia27
Жыл бұрын
Hi why can't I get multiple files in query?
@michaeljasper2225
Жыл бұрын
Trying to finally get up to speed on Power Query (mac) for some what I consider to be hairy workbooks that are too big for their own good because of formulas. First one I am tackling is a workbook that has tabs with sales activity for products/Locations/Day and other supporting data. Each week has its own tab so most years will have 52 tabs. Data is the same. I do want to merge some columns to form a lookup column for future analysis. How do I set all 52 tabs up the same. I have started Power Query and selected all tabs but changes seem to only effect the tab I selected power query from. I would like to avoid the pain of formatting all 52 of the weeks but can if I have to. Then I will use year for part of workbook name and Week number for the tab and try to change the data source each year but still cumbersome. What am I missing. I have run through your videos on Power Query for the Mac but if it is there I missed it. Like you I do have emulation software for windows and have used power query there last year but I like my mac and want to do what I can with that but if need be I will do windows for this yearly roll up with weekly detail.
@jielinlu917
Жыл бұрын
Try restart your PC after updating, the power query editor pops out after restarting.
@user-jc6ot3cj4n
Жыл бұрын
Thanks for sharing Mike. Do you know how to reuse queries on different Excel workbooks for MAC ?
@MikeThomas67
Жыл бұрын
@user-jc6ot3cj4n you'd need to copy the M code that is generated. In the Query Editor, click on the View tab and select Advanced Editor. Select all the copy and copy it. In the other Excel file, Select Data > Get Data > Launch Power Query Editor Click on the View tab and select Advanced Editor Paste and amend the code if necessary
@JawwadRezanoorGenesis
8 ай бұрын
Want to learn about "use first row as header" for multiple sheets where the respective first rows need to be removed and made into one single header for the master columns
@joanmty
7 ай бұрын
Thanks Mike for this video. i learned to use power query because of your video. I just have one doubt. How do you make the column sizes (width) not change every time you refresh power query?
@MikeThomas67
7 ай бұрын
@joanmty Glad you found my video useful and it helped you to learn Power Query. Check out this other video to answer your question: kzitem.info/news/bejne/ka6ttGGDmqFndYY
@Ravi-xq1vu
2 ай бұрын
Loved your video. Have a query: I have a General ledger report in Excel with list of transactions for each Account code in a row followed by transactions for that Account. How can I transform this to add the account code in a new column for each transaction and then remove all the rows with account code heading? Would appreciate your response. thanks
@Ravi-xq1vu
2 ай бұрын
Just to clarify, For each account, there is a header row with Account Code and description which is then followed by number of transactions for that Account. I need to add the account code that is in the header for each transaction in an additional column and then remove the header rows. The output is just list of transactions with account code for each transaction, which can be used for pivot tables etc. Thanks
@PaoloZubani
Жыл бұрын
Hi Mike. I have Excel 365 V16.68 (Ita language) and I don't see the "editor power query Botton". do you have suggest ? Thanks in advance
@MikeThomas67
Жыл бұрын
@paolo Zubani are you signed up to the Beta Channel? As far as I know Power Query Editor is only available to 365 Beta Channel users right now. It will eventually go mainstream
@Aries04kris
2 жыл бұрын
Hi mike I notice on your get data I have all the option except launch power query editor. I have m1 and have the beta insider also looks like I’m up to date… do you know any work around? I’ve tried uninstalling and downloading the app through App Store which that didn’t work either…
@Aries04kris
2 жыл бұрын
Towards the end of the thread it said When it comes to new features, not all beta channel users will see the new feature. Some users get the new feature while the rest is a control group… guess I’m one of them now I’m sad
@MikeThomas67
2 жыл бұрын
@TakenByWine I don't know of any workaround other than to be patient. Also from what I read on a MS forum, Mac App store versions can not join the beta channel. Only Office 365 subscribers of Office purchased elsewhere are eligible for the beta program.
@olegpazynitch28
8 ай бұрын
thanks so much for your course, Is it possible to distribute applied steps for the other tables(queries) automatically? thanks
@MikeThomas67
8 ай бұрын
@olegpazynitch28 Glad you found the course useful. Can you please explain a little more what your question is. Thanks
@olegpazynitch28
8 ай бұрын
Thanks so much for replay, I am beginner for Power Query, i am trying to make one table from many others the same format tables, after clean up one i would like to distribute the same cleaning steps for the others, not sure how to make such script, thanks
@MikeThomas67
8 ай бұрын
@olegpazynitch28 In the Query Editor you can click View (on the Ribbon) and then Advanced Editor. Here you will see the script for the entire Query. You can copy this, paste it into another file using the same command (View > Advanced Editor) and make any changes specific to that file
@olegpazynitch28
8 ай бұрын
@@MikeThomas67 Thanks so much
@BernardoCordoba
6 ай бұрын
Hello Mike! Your video provided excellent information. I have a related question: Is it possible to establish a Power Query connection to a web service such as OneDrive for Business or to a shared library on a SharePoint Online site in Office 365? Currently, I have an Excel file with multiple Power Query connections, all stored in OneDrive and synchronized to my Mac. However, when another user with permissions syncs the same directory and opens the Excel file, they cannot update it because the Power Query connections are local to my machine (OneDrive). Thank you for your assistance. Note: This text has been translated from Spanish to English using ChatGPT 3.5.
@MikeThomas67
6 ай бұрын
@BernardoCordoba Unfortunately not at the current time because Power Query on Mac doesn't support web connections. You could try Import from Web as detailed in this video. No guarantee it will work but worth a try: kzitem.info/news/bejne/s3t81nifhWphdoo&ab_channel=MikeThomas
@ricardocampos72
2 жыл бұрын
Hello I did the update on my excel I have the version 16.64 (22070301). But I can't see the Power Query only "GET EXTERNAL DATA"
@MikeThomas67
2 жыл бұрын
Hi Ricardo. Are you using 365? Did you install from the App Store or from office.com? Are you on the Beta Insider Channel? Rather than repeat myself, please check out the comments thread below between myself and Run Dem Red. Does that help?
@lesliesecord3012
Жыл бұрын
Morning, I have been able to import my data (txt) using the PowerQuery function, but I am unable to clean the data after loading. I have multiple columns, too many spaces and number in text columns and text in number columns. Lost
@MikeThomas67
Жыл бұрын
Hi Leslie. Please contact me via theexceltrainer.co.uk/contact/
@linh198x
Жыл бұрын
I am so stuck right now because i've tried many ways following below your comment but could not set up the Power Query :( Do you have any suggestions for me? Thank you so much. I am truly appreciated.
@MikeThomas67
Жыл бұрын
Hi @linh198x I'm happy to take this conversation offline. Please contact me via theexceltrainer.co.uk/contact/ and I will reply. -Mike-
@MrCalusor
Жыл бұрын
so, we can not import a folder with many excels? so we can have all excel in one sheet?
@MikeThomas67
Жыл бұрын
Unfortunately at the current time you can't import a folder of files like you can in Excel for Windows
@999GiantsFan
9 ай бұрын
Hi Mike! Thanks for an informative session!! I need help with changing an entire column of data the CSV file represents percents as a raw number -- 26.1 representing what should be 26.1% is the way to either add the % sign or change the numbers so they convert to percentages right now when I asked for percent I got 2610%
@MikeThomas67
9 ай бұрын
@999GiantsFan This is what I would do...In the Query Editor, select the column with the percentages. Click Transform on the Ribbon. Select Number Column> Standard > Divide. Type 100 and click OK. You can then either close the Query Editor and format the cells as % using the normal Excel formatting OR Change the number format of the column to Percentage (in the Query Editor) and then close the Query Editor but don't format the cells to have a % sign
@999GiantsFan
9 ай бұрын
@@MikeThomas67 Thank you so much!!
@999GiantsFan
9 ай бұрын
@MikeThomas67 I am getting an error that the web query will not accept the data and asks that I edit the data. Is there a workaround or steps I can take to overcome the issue? Aside from CSV downloads and edits? And, did I miss how to combine tables in the Excel Query editor for those CSV files I have already downloaded because of the data error? If so, point me to the point in your instruction where I can relearn the process. Thanks!!
@MikeThomas67
8 ай бұрын
@999GiantsFan Let's take this offline. Drop me a line via theexceltrainer.co.uk/contact/
@andreshenriquez777
2 жыл бұрын
Hi Mike I have Excel 16.62 and still don't have the Power Query feature. How can I fix this? Please
@MikeThomas67
2 жыл бұрын
Are you on the Beta Channel? Start a new workbook > Help menu > Check for updates > Click Advanced and set the update channel dropdown to Beta. if you are already on the Beta channel, Microsoft roll these updates out in batches. It might not be your turn just yet. All I can say is keep hitting update (like when you keep hitting refresh)
@poppyred7411
2 жыл бұрын
@@MikeThomas67 I am glad I found your video. I have been struggling the whole day until I saw your video and the comments. Restarting the computer after the updates helps. Thank you!
@MikeThomas67
2 жыл бұрын
@@poppyred7411 You are very welcome
@first0last0
11 күн бұрын
So, what's eqivalent to: Power Query - Transform - Standard - Divide?
@MikeThomas67
7 күн бұрын
@first0last0 Sorry what do you mean what's equivalent? Power Query - Transform - Standard - Divide is in the Mac version
@christopherskyap8985
10 ай бұрын
Hi Sir, if I accidentally delete the loaded query, I can't find the query to load to again ?
@MikeThomas67
10 ай бұрын
@christopherskyap8985 If you have deleted a Query unfortunately there is no way to retrieve it, unless the file is stored somewhere like OneDrive or SharePoint where you can roll back to the previous version but then you would lose any other changes that you had made.
@isabellenamnoum4392
2 ай бұрын
can anyone direct me on how I would insert the power query into a pre-existing sheet rather than a new sheet? I can't find anything online that is specific to power query for excel users on this. Thank you!!!!!!
@MikeThomas67
2 ай бұрын
@isabellenamnoum4392 You're not missing anything. it can't be done in Excel for Mac right now
@yadude101
2 жыл бұрын
I have a Mac M1. at 5:30, I don't see the options that you have for 'get data' from powerquery. I only have from html, from text, and new database query from sql, and database, how do I see get data powerquery?
@MikeThomas67
2 жыл бұрын
At the moment this feature is only available to Microsoft 365 users on the Beta Channel using a certain version and later of Excel. What version of Excel do you have? Are you on the Beta Channel? To check what version you are on, click Excel (from the Menu Bar) and About Microsoft Excel. To join the Beta Channel, start a new workbook and click the Help menu. Then click Check for updates and then click Click Advanced. To switch to the Beta Channel set the update channel dropdown to Beta. if you are already on the Beta channel, Microsoft roll these updates out in batches. It might not be your turn just yet. All I can say is keep hitting update (like when you keep hitting refresh)
@run Dem Red This feature is available to Beta Channel users running Version 16.50 (Build 21050400) or later. What does the licence say (just under the version number in the About dialog box)?
@yadude101
2 жыл бұрын
@@MikeThomas67 It says, "License: Microsoft 365 subscription" -thnx
@MikeThomas67
2 жыл бұрын
@run Dem Red In Excel, click Help and choose Check for Updates and click Advanced button. What does the Update Channel dropdown say?
@andresarrieta8104
Жыл бұрын
Is it possible to import data from a web site?
@MikeThomas67
Жыл бұрын
Hi @andres Arrieta, unfortunately at the current time you can't
@aleli1997
Жыл бұрын
What about if I want to open a file with different folders inside with data?
@MikeThomas67
Жыл бұрын
Can you explain a little more about your scenario. Thanks
@aleli1997
Жыл бұрын
@@MikeThomas67 Hi Mike, thanks for reaching back! Scenario: I have a file with multiple data of excel spreadsheets. How can I pick them all at ones? Instead of adding them one by one (MAC os)
@MikeThomas67
Жыл бұрын
@lavh life If you mean you have an Excel file with multiple sheets...once you have selected the file in the first step, in the next step, after clicking Load, you can choose which sheets to inlcude/import
@aleli1997
Жыл бұрын
@@MikeThomas67 thank you!
@waleedaftab4464
Жыл бұрын
Can we import from a PDF file in MAC
@MikeThomas67
Жыл бұрын
@waleedaftab4464 Yes but not using Power Query. I have another video that covers this: kzitem.info/news/bejne/0X6o3J6CaneDfGk&ab_channel=MikeThomas
@waleedaftab4464
Жыл бұрын
@@MikeThomas67 ok thanks! Will definitely look into it
@Wanderer2035
9 ай бұрын
Why dont you show us how you got all that data into your csv file? This tutorial is pointless for us noobies if you dont show us how you got all that data
@MikeThomas67
9 ай бұрын
@wanderer1966 You can create a CSV file manually by opening a text editor (for example TextEdit on Mac, NotePad on Windows) and manually typing the data in, separating each item with a comma (that's what the C in CSV stands for) but in the real world nobody does that. CSV files tend to be generated automatically and are the result of an export/data dump from another application such as a sales application or an HR application or even in your personal life, your online banking application You log into the application and typically select Export and choose CSV as the export format. And voila, you have a CSV file that you can then load into Excel Does this help?
Пікірлер: 83