Unpivot Any Data With A single Function
Автор: Officeinstructor
Загружено: 2023-04-12
Просмотров: 2840
Pivoted Data has Column Headers & Row Headers. It cannot be sorted, Filtered or even used for creating relationships in a Data Model. To do all that, it must be Unpivoted, which is an important functionality in Power Query.
In this tutorial, I show you how to unpivot data by using a single nested function that can be applied to any list or table and refreshes automatically when source data changes.
Unpivoting with Power Query is without any doubt the easiest way to Unpivot. However, the goal of this tutorial is to explore how to build a Single Cell Function and learn how to use 10 different Dynamic Array Functions.
So let’s see how to do that in Excel.
To save the function as an Autocorrect entry:
1- Copy the entire function after REMOVING the NEW Line space. The Function MUST be on ONE line.
2-Go to File ►Options► Proofing ►AutoCorrect Options... ► A dialog box opens
3-Type an abbreviation in the Replace box (can be unp for unpivot)
4- Paste the copied function in the With box ► Hit OK twice
5-To use the function, select the destination cell
6- Type the abbreviation followed by a SPACE ► The Function replaces the abbreviation.
7- Before hitting Enter ► Replace the Source range (Variable A)
8- Hit ENTER and enjoy.
Доступные форматы для скачивания:
Скачать видео mp4
-
Информация по загрузке:
