Join 400,000+ professionals in our courses here https://link.xelplus.com/yt-d-all-courses

In this video you'll learn how to switch data in columns to rows. Pivoting columns in Excel is a common problem when cleaning and organizing data. In this case however, we have a more complex scenario where we have multiple values when pivoting data. We'll have to combine these multiple values into one cell. I'll show you 3 methods to get this done. We'll use Dynamic array Formulas, Power Query (Pivot columns with multiple records) and finally Power Pivot and DAX (CONCATENEX together with HASONEFILTER).

⬇️ Download the workbook here: https://pages.xelplus.com/transform-table-file

We'll tackle a common data organization challenge in Excel—converting data in columns into rows. The scenario involves multiple departments working on various projects, with the goal of presenting this information in a dynamic and structured way.

Key Steps Covered:
Using Formulas: We start by utilizing Excel formulas, such as UNIQUE and FILTER, to extract unique values and handle cases where multiple people from the same department work on the same project.

Power Query: We explore the Power Query approach, demonstrating how to pivot columns and handle cases with multiple values in a cell.

Power Pivot and DAX: For more advanced users, we dive into Power Pivot and DAX to create a measure that dynamically concatenates values based on project and department, providing a highly flexible solution.

00:00 Transform Data in Tables from Columns to Rows
01:10 How to Use Formulas to Switch Data from Columns to Rows
05:51 How to Use Power Query to Pivot Columns with Multiple values
10:27 How to Use Power Pivot and DAX to create a measure with multiple values
16:33 Wrap Up

My Online Excel Courses ► https://www.xelplus.com/courses/

More Power Query videos: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-

️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join

Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/

Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

RESOURCES I recommend: https://www.xelplus.com/resources/

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
LinkedIn: https://www.linkedin.com/company/xelplus

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel