7 Interesting Power Query Tricks

- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

- - - - Blog - - - -

[skimpylemon]

Another great Tip I have learnt is being able to use Microsoft Excel when the PowerQuery Editor is open. To open a new instance of Excel if the PQ editor is open and being worked on, follow these steps:

1. Head to the window button on the task bar
2. Look for the MS Excel Icon (Don't click on it yet)
3. Hold down the Alt button on your keyboard
4. Now click on the MS Excel icon whilst also holding down the Alt button on your keyboard.
5. A dialog window will pop-up asking if you want to open another instance of Excel, Click YES!
6. You have successfully opened Excel whilst the PQ editor is open!!!
7. Enjoy! 😊

[hrithikzgirl]

#6 was the best! I was looking for a way to make sure others wouldn't be able to go in an mess with the queries. This was great! Thanks!

[malchicken]

I like the protect workbook tip, thank you :) . Best tip I recently heard: while power query editor window is open in Excel, top open *another* Excel workbook and 2nd power query window, right click your Excel icon and hold down “Alt” while pressing to open Excel and it will open a new ‘instance’ of the application.

[SMITDEDHIA]

One of my favorite Power Query trick (on similar lines) is

In case of queries if it has a lot of steps, it can be irritating. Often, a lot of those have to be 'Changed Type' step at times. And so each time we add a new column and want to avoid adding a new step just to change type, we can simply suffix "type text" / "type number" etc. after a comma at the end of the current step.

So in stead of
= Table.AddColumn(Source, "Custom", each "Goodly is amazing.")
And then adding a new step to select Text type for that column,

We can do :
= Table.AddColumn(Source, "Custom", each "Goodly is amazing.", type text)

[collontomlyn]

The function lookup w/ documentation is a lifesaver--thank you!

[Jahspecs]

Excellent video! I like your teaching.

[ahmedoufkir]

thanks a lot for these trips
I appreciate your share
That's will help me to automate my work

[syrophenikan]

Didn’t know the last one about copying the M code without opening the query. That will definitely save me some time. Thanks.

[johnsyler]

Thanks! I appreciate all of the work you do in providing these videos. They are awesome. I feel terrible that I accidentally hit dislike on one where I was trying to hit Keep up the great work.

[MANDEEPSINGH-bjhq]

Very Nice tips, thanks. Just one suggestion, you're going too fast. Please go slow & steady.

[wayneedmondson]

Hi Chandeep. Great videos and tips! Thanks for sharing them. Thumbs up!!
PS - Another tip is to right click on any Applied Step and rename it as needed for easier recognition in the future.

[lukereds]

Hi Chandeep, if you protect the query with the command you showed (trick number 6), it's again possible: right click on the query in excel - and you can paste the query and watch it

[kaushikbarot]

I have a huge excel file that requires filtering a lot of data. Important data is very limited based on three or four criteria. It takes a lot of time to load the data and filter. I tried all available tricks, fast load, stopping auto-detect the type, stopping background refresh, etc. I tried removing unnecessary columns first and then filtering as well as vice versa but no luck. Is there any proven way? I will appreciate it.

[mikelennon]

What is the best way to update the records in the source table/sheet form the Power Query ( like, adding records, deleting a record, Can we make a column properties ( one or more columns) a Hyperlink that be clicking it, a custom data entry form be opened to add, deleted or update the record/s in the source tables?

[mayureshghule]

Amazing info...i have pulled data from the site to power query...the data refresh every 3-5 on site...i want to protect the query but protect worksheet don't allow to refresh the data and gives msg 'the download failed'...what should i do?

[caseyconner]

Is there a way when you are building a query to have it not reload the whole thing for example with OLEDB connections when I add a step it queries the whole database and runs everything again even if it's a step after the rows are loaded... with big tables sometimes i am waiting 10+ minutes for it to refresh the preview on steps I add and it's super irritating... lol

[AlexRuu]

Thank you for the tips, this video is taking my M language skills to the next level with a simple explanation.

[arfatlochanco]

Aap Power Query and Advance Excel ke liye batches start kar sakte ho.

[mariodinoia]

Amazing tricks Goodly
I'm not actually sure how many times you actually said "Actually"
But seriously, very useful information

[mitultank]

4:00 If I want to delete this new query. Then how?