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!
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
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.
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?
@joanmty Glad you found my video useful and it helped you to learn Power Query. Check out this other video to answer your question: ua-cam.com/video/0xwN-Mao6BQ/v-deo.html
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
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
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!!!!!!
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.
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
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
@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.
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.
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?
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)?
@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
@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
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.
@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: ua-cam.com/video/REFpDiL80CU/v-deo.html&ab_channel=MikeThomas
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?
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…
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
@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.
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)
@@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!
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
@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
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%
@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
@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 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)
@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
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
@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?
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!
@anna Glad to know you're a happy learner!
This exactly was my issue, hope I see what I’m looking for here.
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
Thanks Charlie. Hope it converts you from a semi-occasional Excel user to a more-than-semi-occasional Excel user :-)
Brilliant video. I'm an advanced Excel user and I finally decided to explore this - I'll start to use it immediately!
@chasmoore1746 Thank you and glad I could help
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.
You're very welcome!
Mate, you're a legend
Very great video, thanks for your time
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?
@joanmty Glad you found my video useful and it helped you to learn Power Query. Check out this other video to answer your question: ua-cam.com/video/0xwN-Mao6BQ/v-deo.html
Try restart your PC after updating, the power query editor pops out after restarting.
Excellent video - Many thanks and easy to follow
Glad it helped
Hi why can't I get multiple files in query?
Great tutorial! Can you load just the merge query on MAC? When I hit close and load it does all of the sheets. Thanks!
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
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
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!!!!!!
@isabellenamnoum4392 You're not missing anything. it can't be done in Excel for Mac right now
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.
So, what's eqivalent to: Power Query - Transform - Standard - Divide?
@first0last0 Sorry what do you mean what's equivalent? Power Query - Transform - Standard - Divide is in the Mac version
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
Possible to share the file you are using during the tutorial because it is easy for us to practise during the training，many thanks!
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
Hi Leslie. Please contact me via theexceltrainer.co.uk/contact/
Hi Sir, if I accidentally delete the loaded query, I can't find the query to load to again ?
@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.
Is the file you've used available to download?
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.
Hi @linh198x I'm happy to take this conversation offline. Please contact me via theexceltrainer.co.uk/contact/ and I will reply. -Mike-
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?
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)
@@MikeThomas67 thnx. it says version 16.53, ©2021
@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)?
@@MikeThomas67 It says, "License: Microsoft 365 subscription" -thnx
@run Dem Red In Excel, click Help and choose Check for Updates and click Advanced button. What does the Update Channel dropdown say?
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
@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
Thanks for sharing Mike. Do you know how to reuse queries on different Excel workbooks for MAC ?
@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
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.
@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: ua-cam.com/video/REFpDiL80CU/v-deo.html&ab_channel=MikeThomas
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"
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?
so, we can not import a folder with many excels? so we can have all excel in one sheet?
Unfortunately at the current time you can't import a folder of files like you can in Excel for Windows
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…
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
@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.
Hi Mike I have Excel 16.62 and still don't have the Power Query feature. How can I fix this? Please
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)
@@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!
@@poppyred7411 You are very welcome
thanks so much for your course, Is it possible to distribute applied steps for the other tables(queries) automatically? thanks
@olegpazynitch28 Glad you found the course useful. Can you please explain a little more what your question is. Thanks
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
@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
@@MikeThomas67 Thanks so much
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%
@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
@@MikeThomas67 Thank you so much!!
@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!!
@999GiantsFan Let's take this offline. Drop me a line via theexceltrainer.co.uk/contact/
Can we import from a PDF file in MAC
@waleedaftab4464 Yes but not using Power Query. I have another video that covers this: ua-cam.com/video/pHrvjL1ERI4/v-deo.html&ab_channel=MikeThomas
@@MikeThomas67 ok thanks! Will definitely look into it
What about if I want to open a file with different folders inside with data?
Can you explain a little more about your scenario. Thanks
@@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)
@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
@@MikeThomas67 thank you!
Is it possible to import data from a web site?
Hi @andres Arrieta, unfortunately at the current time you can't
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
@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?