POWERBI TO PIVOT OR UNPIVOT GALWAY POWERBI MEETUP

POWERBI - TO PIVOT OR UNPIVOT! GALWAY POWERBI MEETUP GROUP

BIT ABOUT ME • Anne Walsh – freelance trainer AKA The Excel Lady • Based in Galway • Author of “Your Excel survival kit – your guide to surviving and thriving in an Excel world” • www. the-excel-expert. com and www. theexcellady. com

WHAT IS PIVOTED DATA? Months going from left to right Headings down the left Normalised Lots of blank rows

PROBLEM? Pivoted data – very difficult to do anything else with it. e. g. want to get YTD? e. g. want to easily set up budgets and variances ?

CLEAN UP YOUR ACT (OR YOUR DATA) NORMALISED? ISSUES • One record per entry • Unintuitive – e. g. one invoice (number repeated) with multiple products • Data dump from external source • Think vertical • Avoid blank rows/columns • Humans generally don’t set up data like this • Before PQ – very tricky to unpivot • John Walkenbach solution

PQ – RIDES TO THE RESCUE Easy to unpivot data in PQ Two methods: 1. Within PQ – itself 2. Ken Puls “recipe” – Unpivoting Subcategorized data

DEMO ON UNPIVOTING DATA Method One Use inbuilt unpivot option – Transform | Unpivot Columns

DEMO ON UNPIVOTING DATA – METHOD TWO Method Two – Ken Puls recipe Unpivoting Subcategorized data

CONCLUSION Unpivoted Data – Your SOLID foundation. Files and presentation will be uploaded to Galway Power. BI group.