Forum Discussion
Methods to Match Power Query Data (Table) to Existing Worksheet Formatting
Data Appearance from Power Query
Which tools and/or methods are available to the user to populate a pre-formatted worksheet with data from Power Query while preserving that worksheet's formatting (look and feel)? In our initial test, the data was available as a default table and is not marching the worksheet's formatting.
We are testing Power Query and are novice level MS Excel users.
Current Data Transfer Workflow
The data from the source Excel file is used to populate the pre-formatted worksheet. The data is currently pasted as values to preserve the destination workbook's worksheet formatting.
Thanks!
Clint
Clint_E_Hill PQ is all about data extraction, transforming and loading. Formatting is the cosmetic part you do outside PQ. Once you have create the first PQ load into a worksheet you can format it the way you want (in Excel). The next time PQ loads updated information into the same space, it will NOT override the Excel formats.
Let's say your pre-formatted work sheet contains a summary space for a table of 5 X 5 rows and columns and you want it to contain a particular background and font colour.
Do the work in PQ first, load a 5X5 table into the correct space. Set the table option to NOT automatically adjust column width, apply colouring and other formats as desired. Now you can empty the data from the 5X5 space (NOT delete the table), save your file, have PQ update the table and you will note that the formats remain in place.
Long story short: Load data from PQ first, then apply the formats in Excel, then Refresh PQ loaded data.
2 Replies
Clint_E_Hill PQ is all about data extraction, transforming and loading. Formatting is the cosmetic part you do outside PQ. Once you have create the first PQ load into a worksheet you can format it the way you want (in Excel). The next time PQ loads updated information into the same space, it will NOT override the Excel formats.
Let's say your pre-formatted work sheet contains a summary space for a table of 5 X 5 rows and columns and you want it to contain a particular background and font colour.
Do the work in PQ first, load a 5X5 table into the correct space. Set the table option to NOT automatically adjust column width, apply colouring and other formats as desired. Now you can empty the data from the 5X5 space (NOT delete the table), save your file, have PQ update the table and you will note that the formats remain in place.
Long story short: Load data from PQ first, then apply the formats in Excel, then Refresh PQ loaded data.
- Your response is just the knowledge boost my team and I needed. Thanks Riny!
