4. The Query Settings window lists the query’s properties and applied steps.
Transform data On the center pane, right-clicking a column displays the available transformations. Examples of the available transformations include removing a column from the table, duplicating the column under a new name, or replacing values. From this menu, you can also split text columns into multiples by common delimiters. The Power Query Editor ribbon contains additional tools that can help you change the data type of columns, add scientific notation, or extract elements from dates, such as day of the week.
Transform data As you apply transformations, each step appears in the Applied Steps list on the Query Settings pane. You can use this list to undo or review specific changes, or even change the name of a step. To save your transformations, select Close & Apply on the Home tab. After you select Close & Apply , Power Query Editor applies the query changes and applies them to Power BI Desktop.
Combine Data From Multiple Sources
Add more data sources To add more sources to an existing report, from the Home ribbon, select Edit Queries and then select New Source . You can use many potential data sources in Power BI Desktop, including folders. By connecting to a folder, you can import data from multiple Excel or CSV files at once. Power Query Editor allows you to apply filters to your data. For example, selecting the drop-down arrow next to a column opens a checklist of text filters. Clearing a filter allows you to remove values from your model before the data is loaded into Power BI.
Merge and append queries You can also merge and append queries. In other words, Power BI pulls data that you select from multiple tables or various files into a single table. Use the Append Queries tool to add the data from a new table to an existing query. Power BI Desktop attempts to match the columns in your queries, which you can then adjust as necessary in Power Query Editor.
Write customized queries You can use the Add Custom Column tool to write new customized query expressions by using the powerful M language.
Clean Data to Include in a Report
Clean data Fortunately, Power Query Editor has tools to help you quickly transform multi-column tables into datasets that you can use. Transpose data By using Transpose in Power Query Editor, you can swap rows into columns to better format the data.
Format data You might need to format data so that Power BI can properly categorize and identify that data. With some transformations, you'll cleanse data into a dataset that you can use in Power BI. Examples of powerful transformations include promoting rows into headers, using Fill to replace null values, and Unpivot Columns . With Power BI, you can experiment with transformations and determine which will transform your data into the most usable columnar format. Re- member, the Applied Steps section of Power Query Editor records all your actions. If a transformation doesn't work the way that you intended, select the X next to the step, and then undo it. After you've cleaned your data into a usable format, you can begin to create powerful visuals in Power BI.
END By: Jewel Anne R. Atanacio
You've reached the end of your free preview.
Want to read all 35 pages?
- Winter '20
- Type system, Power BI service, Explore Power BI Desktop