Appending queries

We now have three separate tables for hours data reported by employees, one of each for January, February, and March. However, what we really need is for all of this data to reside in a single table. We can accomplish this by using an Append query, as follows:

  1. Start by clicking on the Home tab of the ribbon.
  2. In the Combine section at the far right of the ribbon, choose the Append Queries dropdown and choose Append Queries as New. This displays the Append queries dialog, as shown here:

Figure 17: Append queries dialog
  1. In the Append queries dialog, select the radio button for Three or more tables.
  2. Use the Add >> button to add the January, February, and March queries to Tables to append. The order does not matter:

Figure 18: Appending three or more tables
  1. When finished, click the OK button.

This procedure creates a query called Append1. Note that the Append1 query consists of a single step, Source. The formula that's displayed in the Formula Bar for this step is as follows:

= Table.Combine({January, February, March})

We will now put some finishing touches on this query by performing a few additional steps, as follows:

  1. Select the query called Append1 in the Queries pane.
  2. Right-click the query and choose Rename. Rename this query to Hours.

The Hours query now contains all of the information from the January, February, and March queries. This means that we can disable loads on each of these queries. To do this, perform the following steps:

  1. Right-click on each of these queries and uncheck Enable load.
  1. A warning dialog appears, informing us that this operation will not load data from this query into its own table and will actually remove the table from our data model if it exists. Click the Continue button.
  2. Finally, locate the Hours column in the Hours query. Right-click the header for this column and choose Change Type and then Decimal Number. This ensures that Hours comes in as a Decimal Number and not a Fixed Decimal Number or currency. This operation is shown in the following screenshot:

Figure 19: Changing type to decimal number
If you have many files in the same format, consider using a Combine Binaries (Folder) query: https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.149.28.9