Clearly and Simply

Category: Power Query

  • Adjust Power Query Table Style and Properties

    How to adjust the Default Table Style and the Autofit Column Width Property manually and automatically for all Query Tables in a Workbook

    960 words, ~5 minutes read

    Power Query Table Properties - IntroVery often, you get and transform data using Power Query and then load it into a table on a worksheet of your Excel workbook. When loading it for the first time, Power Query makes some decisions for you. Regarding the properties of the query table, but also regarding the used table style.

    Most of theses decisions are fine. Especially two of them can become a pain in the neck, though.

    First one is autofitting the column widths of the sheet to fit to the size of the header and the data. This is great, if you have only the table itself on the sheet, but it can be annoying, if you have also texts or calculations above the query table.

    Second issue is the table design. Power Query uses the built-in Green Table Style Medium 7, not the default Table Style of the workbook.

    I do not know of a way in Power Query to change these settings, so you always have to adjust your query table after the first load.

    Today’s post describes how to do these adjustments manually for one query table and also provides a ready-to-use VBA code snippet to change properties and styles for all tables in a workbook in one go.

    (more…)

  • Create a Summary Table in Power Query

    How to create a Summary Table in Power Query with aggregated values of the original data source without importing the entire data into Excel

    A frequent use case of Power Query (aka Get and Transform) is to connect to an external, big data source, filter and remove data in a query and load only a fraction of all rows into the Excel workbook.

    This will ensure you only carry along the parts of the data in your workbook you really need and will thereby keep the size lean and the performance fast.

    Power Query Summary Table Intro

    Although you do not need the original data on row level in your workbook, you might be interested in a couple of aggregated measures of the original data table, e.g. the total sum of a column, the count of rows, the distinct count of entries (unique values) in a column, etc.

    Today’s post explains how to create a Summary Table with defined aggregations on the original data without loading the entire source. The key of the solution is the M-function #table.

    As always, the post comes with an example workbook for free download.

    (more…)