Format Rows and Columns in Excel Power Query Based on Source Table

0
votes

I am trying to integrate several tables across multiple worksheets, but all in one workbook. I am currently using Power Query to get data from tables on all the sheets to appear in an overview on the first sheet.

For example, consider the following:

Table 1 - 

Date        Time        Note
01/02/03    13:59       First entry
03/04/05    08:36       Second entry

Table 2 -

Date        Time        Type
02/03/04    19:19       Cold
06/07/08    07:22       Hot

Overview - 

Date        Time        Entries
01/02/03    13:59       First entry
02/03/04    19:19       Cold
03/04/05    08:36       Second entry
04/05/06    07:22       Hot

I am currently able to merge columns together (though I am having trouble when merging columns containing numbers with columns containing text...), as can be seen under "Entries" in the Overview table.

What I would like to do is be able to add another column based on the source for each row in the Overview table.

This would look like:

Overview - 

Date        Time        Entries        Source
01/02/03    13:59       First entry    Table 1
02/03/04    19:19       Cold           Table 2
03/04/05    08:36       Second entry   Table 1
04/05/06    07:22       Hot            Table 2

Additionally, it would be nice if the rows sourced from Table 1 could be in red, while the rows sourced from Table 2 could be in blue.

Is there a way I can use Power Query to format the individual cell contents, as well as entire rows based on the source of entries?

1
excelpowerquery

1 Answers

2
votes

If the tables have the same structure, you can use Append, rather than Merge. Before appending, set the columns to the same data type. I don't quite see how there are any numbers in your text columns, though.

In Power Query:

  • create a query from Table 1
  • add a column called "Source" with the formula ="Table 1"
  • rename "Note" to "Entries" and set it as type "text"
  • Save the query as a connection only
  • create another query from Table 2
  • add a column called "Source" with the formula ="Table 2"
  • rename "Type" to "Entries" and set it as type "text"
  • append the query from above
  • sort as desired
  • save and load to the workbook

In the resulting worksheet use conditional formatting for coloring based on the value in the Source column.