RE-USING M-LANGUAGE SCRIPT FROM POWERBI IN MICROSOFT EXCEL

• There are some Data Analysts that doesn’t make use of PowerBI for visualization but I can say that apart from PowerBI being used for visualization, it is a very powerful tool for data cleaning and preparation. It can performing deep and complicated cleaning compared to Microsoft excel.
• There are some other visualization tools that cannot perform data cleaning.

Photo Credit: Google

• If you are in the category of analysts that doesn’t use PowerBI, I’ll advice you to just learn the cleaning aspect of PowerBI using the power query.
• In this blog, I’ll be discussing how to clean data in PowerBI with power query and re-use the language script in Microsoft excel power query so that you can load the cleaned excel workbook into any visualization tool you want to use.
• We will be considering the dataset from Kaggle titled “Nigerian Used Car Marketplace”. Link to the dataset will be given below.
• We will be performing basic cleaning operations by doing the following:

1. renaming the column headers for better understanding.
2. changing the data type for the year column from number to text (it is always good to change the data type from number to text if the feature cannot perform aggregate operations e.g. postal code, phone number, year(like in our case)…etc.).

Steps involved are:

1. Get the excel workbook into PowerBI desktop from the Get data pane.

2. Tick the worksheet box and click on transform data. When this is done, the power query editor will be opened.

3. Rename each column to your desire by right clicking the column header and clicking on rename.

4. Change the data type for the year column header by clicking the number icon close to the column header and changing to text.

5. After doing this, you will see the list of applied steps to your right.

6. Locate Advanced Editor pane either on the home or view tab of the power query editor and click on it, you will see the M Language script. Select everything and click CTRL+C. This means you have copied the entire code.

picture depicting the cleaning steps and edited workbook

picture depicting the M-Language script in powerbi power query editor

• To reuse this in Microsoft Excel, open the excel workbook in Microsoft excel and do the following steps:

1. Click the power query tab

2. Locate from other sources pane, click the drop-down icon and select blank query. This will open the power query editor.

3. Locate Advanced Editor pane either on the home or view tab of the power query editor and click on it. Delete whatever is there and paste the code that has been copied from PowerBI and click done.

4. After it has been loaded for view, go to the home tab in the power query editor and click on close and load. The worksheet will automatically change to the same way it was in PowerBI after cleaning without doing any manual editing. The cleaned version will be in a new worksheet.

picture depicting the pasted script in microsoft excel power query editor

picture depicting the excel workbook before applying the script

picture depicting the excel workbook after applying the script

N.B: if you are using Microsoft Excel 2013 which night not have power query, go online and download “power query 2013” for the bit size of your Microsoft excel.

To check your excel version and bit size, go to file, click on account and click on about excel.

The link to the dataset is : https://www.kaggle.com/datasets/sootersaalu/nigerian-used-car-marketplace

If while learning, you feel the need to ask questions for clarity, you can send me a mail @marybajisma123@gmail.com or whatsapp 09021833246.