Convert Columns to Rows in Excel (NO Transpose, NO Formulas - SIMPLY UNPIVOT in Power Query)
19 September 2019 Excel Power Query (Get & Transform)
Check Out the Complete Excel Power Query course: https://www.xelplus.com/course/excel-power-query/
It sounds simple to just switch columns to rows - basically transpose horizontal data to vertical. BUT you can't always use a simple transpose (neither the transpose feature nor the Transpose function) if you only need to transpose parts of your data set. Some columns, instead of all columns.
For example if you have a report in Excel with a list of months in the columns, it would be easier to analyze the data and create formulas or pivot tables on the data, If your data is in a tabular format. For each value field in the original report, we will get a new row. Since this involves unpivoting or transposing parts of the data, it can be difficult to achieve with Excel formulas.
This is where you can use Excel's "Get & Transform" feature - (also called Power Query). Get & Transform is available since Excel 2016. All you need to do is create a table or a named range from your source data and then unpivot the columns in the Power Query editor.
If some of the value cells in your report happen to be empty - the entire row will be removed in the final table. To keep empty cells, you need to replace the null value in Power Query with 0.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-powerquery-transpose-unpivot/
LINKS to related videos:
3 ways to transpose: https://rumble.com/v2ua37m
R1C1 trick to transpose: https://rumble.com/v2ub0do-
Unstack data: https://rumble.com/v2ua4uc
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
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!
#MsExcel #Excel #LeilaGharani
-
1:17:51
Man in America
12 hours ago🔴 RED ALERT: Military Now Authorized to KILL Americans on U.S. Soil? Ron Paul's Dire Warning
56.3K134 -
3:44:58
Pepkilla
8 hours agoWho's Ready for Black ops ?!?!
48.5K9 -
2:11:47
Laura Loomer
7 hours agoEP83: MAGA on FIRE as Kamala Flames Out
56.9K29 -
1:33:54
Flyover Conservatives
1 day agoBO POLNY & CLAY CLARK | Buckle Up! October’s Shift Could Change America Forever! | FOC Show
63.8K14 -
1:04:24
The Anthony Rogers Show
15 hours agoEpisode 343 - Are Parasites Making Us Sick?
37.5K9 -
1:17:59
Kim Iversen
10 hours agoShocking U.S. Warning to Israel — “No Aid, No Weapons!" | Kamala Makes Desperate Plea to Black Men Voters
77.4K190 -
7:32:25
FusedAegisTV
1 day ago『AST lvl 100』Tuesday Raid & Rant | Final Fantasy XIV | Harris Pitches Illegal Racism, Arcadion Raids
51.1K4 -
1:37:00
Glenn Greenwald
11 hours agoKamala's New Reliance On John Bolton & Other Warmongers; Liberal Attack On Disobedient Non-White Voters; Nick Cruse On Kamala's Desperate Pandering | SYSTEM UPDATE #353
96.4K103 -
1:21:17
Donald Trump Jr.
15 hours agoJust Like Biden, Kamala has Her own Plagiarism Scandal. Plus, Taking Your Questions Live! | TRIGGERED Ep.182
148K200 -
8:30:33
Right Side Broadcasting Network
3 days agoLIVE REPLAY: President Trump Delivers Remarks in Atlanta, GA - 10/15/24
265K117