Category: Power Query

Posted on

Happy Power Query Christmas

(Names, Behaviours) =>
let
    #"Make List" = Table.FromColumns({Names,Behaviours}, {"Name", "Behaviour"}),
    #"Check Once" = Table.SelectRows(#"Make List", each ([Behaviour] <> "Naughty")),
    #"Check Twice" = Table.SelectRows(#"Check Once", each ([Behaviour] = "Nice")),
    #"Santa Claus" = if Table.RowCount(#"Check Twice") > 0 then "is coming to town" else null
in
    #"Santa Claus"
Posted on

Power Query: Data Transformation of Dynamic Columns

When you manually select all columns and apply a transformation (for example, changing the data type) the generated code lists each column name individually – it looks something like this:

// Generated code from manual actions
=   Table.TransformColumnTypes ( 
        #"Previous Step",
        {
            {"First Column", type text}, 
            {"2nd Column", type text}, 
            {"Col 3", type text}, 
            {"COLUMN FOUR", type text}, 
            {"Number Five (is alive)", type text}, 
            {"666", type text}
        }
    )

I frequently import data from files with varying column names, and / or varying numbers of columns. This makes it impossible to effectively hard code data transformations by column name – if somebody renames any of those columns, then the step above will fail.

So, instead of the generated steps to change each column type, we can use the List.Transform function to apply a transformation to each Column:

//Change All Columns to text
=   Table.TransformColumnTypes ( 
        #"Previous Step", 
        List.Transform ( 
            Table.ColumnNames ( #"Previous Step" ), 
            each {_, type text}
        )
    )

This example is about as simple as it gets – you can of course apply a broad range of transformation functions in this way – for example, Text.Trim:

//Trim All Columns
=   Table.TransformColumns ( 
        #"Previous Step", 
        List.Transform ( 
            Table.ColumnNames ( #"Previous Step" ), 
            each {_, Text.Trim, type text}
        )
    )
Posted on

Power Query – How To Paste Code (video)

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it.

Here’s a short video explaining how to use that Power Query code you’ve been given.

Posted on

Power Query & Power Pivot

I realise I haven’t posted anything on here in quite a long time. That’s mainly because I’ve spent a long time learning all about Power Excel – and it’s a brave new world!

I used to spend most of my Excel time in the VBA editor. These days, I find I spend all my Excel time using Power Query, and Power Pivot (and I wish Microsoft hadn’t changed their names in Excel 2016!)

Have you learned how to use these power tools, yet? If not, I strongly suggest you take some time to look into them. They make it possible to work with data SO much more easily than in the past, and they’re so much faster to learn and use than VBA / formula solutions.

It’s simple to mash up data from a variety of sources, to reshape it fo fit your requirement, and to join up multiple sources in one view. Creating robust measures in Power Pivot makes it much easier and more reliable for report consumers to modify their views and visualisations, safe in the knowledge that the numbers will stay right, and changing layouts won’t break any of the calculations! They provide a much more transparent solution too, which is easier to share, and to understand.

I’m planning to make some time soon to write some beginner’s guides to Power Query and Power Pivot – in the meantime, this is a great link, explaining some Power Query basics