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

Question

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?

teylyn · Accepted Answer · 2016-01-24 20:20:54Z

2

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.

answered Jan 24, 2016 at 20:20

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hari Over a year ago

I'm not sure what you mean by "the same structure," but I think they do. In my example, there were no numbers, but if you replace "Hot" and "Cold" from my example with the numbers "1" and "2" it would serve to illustrate my point. I will try your solution as is, but please let me know if the additional info changes anything.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related