top of page
Search
Writer's pictureCarlos Altamirano
Oct 134 min read

When to use Excel and when to use Power Query? A practical analysis | Xyclos

Using Power Query in Excel and Power BI | Xyclos




I have published hundreds of articles on my blog on various topics and I wanted to categorize them to have better control over them for future posts.


I obtained the list of blog titles after performing a debugging and selection process in an Excel Workbook.


The titles are well identified and contain the keywords I want to use to categorize them.


I have two options to do it:


Excel

Create a column for each category, where I use the FIND function to identify which blog has a certain word, for example:

  1. In cell C1 I enter Power BI

  2. In cell C2 enter =FIND(C$1, $A2)

  3. In the fill handle I double click to copy the function down

    Ready.

  4. I do the same to create other columns with other keywords


Now we see the process using Power Query , inside Excel :


Power Query


I save the Excel file in a folder.


  1. I open a blank book

  2. I click on the Data tab

  3. Then I click on Get Data / From a File / From an Excel Workbook

  4. I select the folder and the Book

  5. The Power Query Editor is activated

  6. I change the Data Type to Text for the column I'm going to search for the text in. This is one of the very important steps to make everything work in Power Query: set the correct Data Type for each column.

  7. I click on the Add Column tab

  8. Then I select Custom Column

  9. In the Custom Column window in the New Column Name field, I enter the name of the new column: Power BI

  10. In the Custom Column Formula field enter: = Text.Contains([Blog Title], "Power BI") This function has two arguments : the name of the field: Blog Title and the text to search for in the field: Power BI And the result will be: true or false

    Create a custom column in Power Query | Xyclos

  11. I click on the OK button

    New column is added

  12. I change the Data Type of this column to True/False because it is a column that can contain: true , false or null

  13. I repeat steps 7 to 12 to create other columns with other keywords

  14. I click on the Home tab and then click Close and Apply

    Done, I have created a table with the new columns


So far so good, doing it in Excel is faster , but let's do some additional analysis.


What happens when the list is constantly changing?


If you're like me, and you continually publish new articles, your list of titles will be constantly changing and growing. Every time you add new articles, you'll need to update the list and apply the same categorization rules.


This is where the differences between using Excel and Power Query become clear:


Using Excel:

You get the list of new titles and paste it into the sheet where you already have the formulas and functions configured, which in this blog we teach you the differences between them: Formulas and Functions in Microsoft Excel | Xyclos


Next, you need to navigate to the cells where the FIND functions or whatever functions you used are, and double-click the bottom corner of the cell to copy them down, applying the same rules to the new items.


It is a manual process, which while not complicated, can be tedious and error-prone if you handle large amounts of data or need to do this regularly.


Using Power Query:

You just need to save the new list of items in the same location or folder where the original list is located.


Once you have the option to refresh on file opening enabled, you don't need to do anything else. Power Query will automatically update the table with the new articles and apply the same categorization rules without any manual intervention.


The process is automatic and fast, allowing you to save time and reduce the risk of errors, especially if this process is performed frequently.


The big difference: automation vs. manual work


  • Excel is great for one-time updates and when you want manual control over each step.

  • Power Query is perfect for automating repetitive tasks and making them fast and error-free, especially with changing data or large volumes.


Mastering Power Query is not only an advantage in Excel , but it is also a valuable asset in Power BI.


Power Query in Power BI: An essential plus


Additionally, it is important to note that Power Query is not only available in Excel , but is also a key tool in Power BI.


If you work or plan to work with Power BI, mastering Power Query will give you an incredible advantage, as it allows you to transform, cleanse, and prepare data before using it in reports and visuals or visualizations.


By using Power Query in Power BI, you can automate the loading and transformation of data from multiple sources, improving the quality and efficiency of the reports you create. In an environment where business data analysis is increasingly in demand, having a good command of Power Query can make a difference in the quality of your reports and the speed with which you deliver results.


In summary:


While Excel is useful for one-off tasks, Power Query, both in Excel and Power BI, is the way to go if you're looking for automation , efficiency, and a more robust workflow for the future.


The command menu that Power Query has is very extensive and useful to perform tasks such as deleting columns, filtering data, combining queries, joining tables, extracting data using artificial intelligence, options to extract Time Intelligence data, such as the year, the month, day, hour...all this, without having to learn the M language, which is the one we use for this case.


At Xyclos Academy, we have the " Power Query Consolidation of File Tables " course that guides you step by step to learn this powerful productivity tool, which will give you a competitive advantage in the use of Excel and Power BI, improving your ability to transform and analyze data efficiently, which is crucial for any data analyst or business management professional.





We also have these courses for your professional development:






3 views0 comments

Related Posts

See All

Commentaires

bottom of page