Tag: 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 & 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