How to transform data using Power Query: A beginner’s guide

Preliminary setup and getting knowledge

1. Launch the Energy Desktop app in your pc.
2. From the Residence tab or menu, choose ‘Get Information’.
3. From the dropdown menu select ‘Net’.

Inputting the Net URL

1. Enter the URL proven within the ‘From Net’ dialog field under or use this hyperlink.
2. Click on ‘OK’ to proceed.

3. If prompted, choose ‘Nameless Entry’ to connect with the net content material and click on ‘Join.’

Choosing the suitable desk

1. Within the Navigator window, select the desk labeled ‘Particular person issue scores are normalized on a 0-100 scale…’.
2. Click on on ‘Remodel Information’ to open the Energy Question Editor.

Working within the Energy Question editor

1. The Energy Question Editor will open, displaying the default actions within the ‘Question Settings’ panel beneath ‘APPLIED STEPS’ as proven under.
2. You could change the title of the desk to what you would like and the press Enter.

Including and customising columns

• Navigate to the ‘Add Column’ ribbon and choose ‘Customized Column.’

• Within the Customized Column dialog field, enter ‘New rating’ because the column title (or some other title you like) and supply the specified system as proven under.
• Make sure that the standing message on the backside is ready as ‘No syntax errors have been detected’
• Then click on ‘OK’.

Reviewing utilized steps

Examine the ‘Question Settings’ panel to see that the newly added customized step is now listed beneath ‘APPLIED STEPS.’

Adjusting and reworking knowledge

Earlier than continuing with this question, let’s modify it to raised swimsuit our knowledge necessities. We’ll do the next:

• Alter the rankings by eradicating a column.

1. Choose the ‘Climate’ column and take away it by clicking ‘Take away Columns’ from the Residence tab.

2. Proper-click the ‘Eliminated Columns’ step and transfer it above the ‘Added Customized’ step to regulate the order of operations as proven under.

Observe that the New rating column now shows “Error” as an alternative of the calculated worth.

To get extra details about every error, choose the column and Energy Question Editor will show the small print.

Two adjustments are required to repair the error: take away the Climate column title and alter the divisor from 7 to six.

These could be finished in both of the next methods:

• Proper-click the “Added or Column” step and choose “Edit Settings.” It will open the Customized Column dialog used to create the New rating column.
• Modify the system as beforehand described (i.e. take away Climate and alter the divisor from 7 to six).

• Choose the New rating column, then allow the Components Bar checkbox from the View tab to show the column’s knowledge system.
• Modify the system as beforehand described till it seems as follows, then press Enter.

Sorting and changing values

• To type knowledge, choose the ‘Added Customized’ step, click on the dropdown subsequent to the ‘New rating’ column header, and select ‘Type Descending.’

Discover how Energy Question Editor mechanically provides a brand new step instantly after the presently chosen utilized step.

• In APPLIED STEPS, choose the step earlier than the customized column, i.e, Eliminated Columns step.
• Proper-click the cell with Nebraska worth High quality of life rating.
• Choose Change Values.

Energy Question Editor notifies us that subsequent steps may break the question as we’re inserting a brand new step.

• Change the info worth to 70.

• Choose OK. Energy Question Editor now substitutes the info for Nebraska.

Reviewing last adjustments

Discover how the Energy Question Editor names a brand new entry (Changed Column) in APPLIED STEPS.

• Choose the final Utilized Step, Sorted Rows.

Discover the info has modified relating to Nebraska’s new rating.

Please comply with this link for an extra step-by- step information on the way to put together knowledge for a dashboard utilizing knowledge from a spreadsheet software resembling Microsoft Excel.

Wrapping up

This information has offered a fundamental overview of utilizing Energy Question in Energy BI to remodel and handle knowledge effectively.

It begins with the preliminary setup of Energy BI Desktop and explains the way to get knowledge from an internet supply.

Customers are guided by means of inputting a URL, deciding on the suitable knowledge desk, and navigating the Energy Question Editor interface.

The information additionally coated including and Customising columns, together with defining new columns utilizing customized formulation.

It defined the way to evaluation and alter utilized steps, take away pointless columns, and reorder steps to make sure correct knowledge transformation.

It additionally included directions on dealing with errors, sorting knowledge, and changing particular values to satisfy knowledge necessities.

The method concludes with reviewing last adjustments to make sure that all knowledge transformations are accurately utilized,

This information ought to empower rookies to leverage Energy Question’s capabilities to create significant stories and acquire beneficial insights from their knowledge.