{"id":3644,"date":"2024-12-03T03:18:02","date_gmt":"2024-12-03T03:18:02","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/power-query-unpivot-du-lieu\/"},"modified":"2024-12-03T03:18:02","modified_gmt":"2024-12-03T03:18:02","slug":"power-query-unpivot-du-lieu","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/power-query-unpivot-du-lieu\/","title":{"rendered":"POWER QUERY \u2013 UNPIVOT d\u1eef li\u1ec7u"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-h4k0Fa0I#1\">\n<p><span style=\"font-weight: 400\"><em>B\u1ed1 c\u1ee5c d\u1eef li\u1ec7u ch\u00ednh x\u00e1c cho Excel \u0111\u1ec3 s\u1eed d\u1ee5ng? B\u00e0i vi\u1ebft n\u00e0y c\u1ee7a <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd cung c\u1ea5p c\u00e2u tr\u1ea3 l\u1eddi t\u1ed1t cho c\u00e2u h\u1ecfi \u0111\u00f3, b\u1eb1ng c\u00e1ch h\u01b0\u1edbng d\u1eabn chi ti\u1ebft c\u00e1c <a href=\"http:\/\/blog.ezworkapp.com\/power-query-unpivot-du-lieu.html\"><strong>Unpivot d\u1eef li\u1ec7u<\/strong><\/a> m\u1eddi c\u00e1c b\u1ea1n c\u00f9ng \u0111\u1ecdc<\/em>.\u00a0<\/span><\/p>\n<p><em><span style=\"font-weight: 400\">Chu\u1ea9n b\u1ecb d\u1eef li\u1ec7u c\u00f3 l\u1ebd l\u00e0 y\u1ebfu t\u1ed1 quan tr\u1ecdng nh\u1ea5t trong ph\u00e2n t\u00edch d\u1eef li\u1ec7u. N\u1ebfu b\u1ed1 tr\u00ed d\u1eef li\u1ec7u sai, b\u1ea1n s\u1ebd li\u00ean t\u1ee5c ph\u1ea3i chi\u1ebfn \u0111\u1ea5u v\u1edbi Excel; ph\u1ea3i s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c n\u00e2ng cao, th\u00eam c\u1ed9t kh\u00f4ng c\u1ea7n thi\u1ebft ho\u1eb7c th\u1eadm ch\u00ed tr\u00f9ng l\u1eb7p d\u1eef li\u1ec7u. Nh\u01b0ng n\u1ebfu d\u1eef li\u1ec7u \u1edf \u0111\u00fang c\u1ea5u tr\u00fac, Excel s\u1ebd tr\u1edf n\u00ean d\u1ec5 d\u00e0ng. T\u00f4i \u0111\u00e3 t\u1eebng ng\u1ea1c nhi\u00ean v\u1ec1 nh\u1eefng ng\u01b0\u1eddi c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p. T\u00f4i \u0111\u00e3 thu th\u1eadp \u0111\u01b0\u1ee3c r\u1ea5t nhi\u1ec1u m\u1eb9o v\u00e0 th\u1ee7 thu\u1eadt b\u1eb1ng c\u00e1ch c\u1ed1 g\u1eafng h\u1ecdc nh\u1eefng k\u1ef9 thu\u1eadt t\u01b0\u01a1ng t\u1ef1. Nh\u01b0ng t\u00f4i nh\u1eadn th\u1ea5y r\u1eb1ng vi\u1ec7c nh\u1eadn d\u1eef li\u1ec7u \u1edf \u0111\u1ecbnh d\u1ea1ng ch\u00ednh x\u00e1c m\u00e0 t\u00f4i th\u1ea5y hi\u1ebfm khi c\u1ea7n \u0111\u1ebfn nh\u1eefng k\u1ef9 n\u0103ng n\u00e2ng cao n\u00e0y.\u00a0<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">Tr\u01b0\u1edbc Power Pivot, ch\u00fang ta s\u1ebd m\u1ea5t nhi\u1ec1u th\u1eddi gian \u0111\u1ec3 chuy\u1ec3n \u0111\u1ed5i d\u1eef li\u1ec7u; ch\u00fang ta \u0111\u00e3 ph\u1ea3i s\u1eed d\u1ee5ng \u0111\u1ebfn c\u00f4ng c\u1ee5 ph\u1ee9c t\u1ea1p ho\u1eb7c m\u00e3 VBA d\u00e0i. Nh\u01b0ng <a href=\"http:\/\/blog.ezworkapp.com\/power-query-la-gi-tai-sao-can-hoc-power-query.html\"><strong>Power Query<\/strong><\/a> c\u00f3 nhi\u1ec1u t\u00ednh n\u0103ng \u0111\u1ec3 gi\u00fap vi\u1ec7c chuy\u1ec3n \u0111\u1ed5i n\u00e0y tr\u1edf n\u00ean d\u1ec5 d\u00e0ng. M\u1ed9t trong nh\u1eefng t\u00ednh n\u0103ng h\u1eefu \u00edch nh\u1ea5t l\u00e0<strong> unpivot d\u1eef li\u1ec7u<\/strong>, \u0111\u00e2y l\u00e0 nh\u1eefng g\u00ec ch\u00fang ta \u0111\u1ec1 c\u1eadp trong b\u00e0i \u0111\u0103ng n\u00e0y. Qua ba v\u00ed d\u1ee5, ch\u00fang ta s\u1ebd ti\u1ebfn b\u1ed9 t\u1eeb c\u00e1c \u0111\u1ecbnh d\u1ea1ng d\u1eef li\u1ec7u ngu\u1ed3n d\u1ec5 \u0111\u1ebfn kh\u00f3. Nh\u01b0ng n\u00f3 s\u1ebd tr\u1edf n\u00ean r\u00f5 r\u00e0ng m\u1ed9t n\u1ec1n t\u1ea3ng trong m\u1ed9t v\u00e0i ph\u00e9p chuy\u1ec3n \u0111\u1ed5i c\u01a1 b\u1ea3n \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u00fang c\u00e1ch l\u00e0 t\u1ea5t c\u1ea3 nh\u1eefng g\u00ec c\u1ea7n thi\u1ebft.<\/span><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-674e7868d1dd3\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/power-query-unpivot-du-lieu.html#Downloads\" title=\"Downloads\">Downloads<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/power-query-unpivot-du-lieu.html#Unpivot_du_lieu_co_ban\" title=\"Unpivot d\u1eef li\u1ec7u c\u01a1 b\u1ea3n\">Unpivot d\u1eef li\u1ec7u c\u01a1 b\u1ea3n<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/power-query-unpivot-du-lieu.html#Unpivot_du_lieu_voi_nhieu_cot\" title=\"Unpivot d\u1eef li\u1ec7u v\u1edbi nhi\u1ec1u c\u1ed9t\">Unpivot d\u1eef li\u1ec7u v\u1edbi nhi\u1ec1u c\u1ed9t<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/power-query-unpivot-du-lieu.html#Unpivot_voi_nhieu_hang_tieu_de\" title=\"Unpivot v\u1edbi nhi\u1ec1u h\u00e0ng ti\u00eau \u0111\u1ec1\">Unpivot v\u1edbi nhi\u1ec1u h\u00e0ng ti\u00eau \u0111\u1ec1<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Downloads\"><\/span><b>Downloads<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 l\u00e0m vi\u1ec7c c\u00f9ng v\u1edbi c\u00e1c v\u00ed d\u1ee5 b\u00ean d\u01b0\u1edbi, h\u00e3y t\u1ea3i xu\u1ed1ng c\u00e1c t\u1ec7p m\u1eabu. Nh\u1ea5p v\u00e0o Click here \u0111\u1ec3 \u0111\u0103ng k\u00fd v\u00e0 c\u00f3 quy\u1ec1n truy c\u1eadp v\u00e0o ph\u1ea7n Downloads.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c v\u00ed d\u1ee5 trong b\u00e0i \u0111\u0103ng n\u00e0y s\u1eed d\u1ee5ng t\u1ec7p c\u00f3 t\u00ean <\/span><a href=\"https:\/\/drive.google.com\/file\/d\/1LkCuZ2XkFnOSKgtqYR4y3VllpSn1XSlf\/view\"><b>Example 12 \u2013 Unpivot Data.xlsx<\/b><\/a><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd h\u00e3y kh\u1edfi \u0111\u1ed9ng Excel, h\u00e3y m\u1edf t\u1ec7p v\u00ed d\u1ee5. Ch\u00fang ta s\u1eb5n s\u00e0ng!<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Unpivot_du_lieu_co_ban\"><\/span><b>Unpivot d\u1eef li\u1ec7u c\u01a1 b\u1ea3n<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">\u0110\u1ed1i v\u1edbi l\u1ea7n \u0111\u1ea7u ti\u00ean ch\u00fang ta unpivot d\u1eef li\u1ec7u, ch\u00fang ta \u0111ang s\u1eed d\u1ee5ng trang t\u00ednh <\/span><span style=\"font-weight: 400\">Example 1<\/span><span style=\"font-weight: 400\">.<\/span><\/p>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-282.gif\" title=\"ex101_92019-nho-282\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<figure id=\"attachment_34185\" aria-describedby=\"caption-attachment-34185\" style=\"width: 590px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34185 size-full\" title=\"power-query-unpivot-du-lieu-1\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-1-2.png\" alt=\"power-query-unpivot-du-lieu-1\" width=\"590\" height=\"378\"><figcaption id=\"caption-attachment-34185\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-1<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">D\u1eef li\u1ec7u ngu\u1ed3n hi\u1ec3n th\u1ecb m\u1ed9t c\u1ed9t ri\u00eang cho m\u1ed7i th\u00e1ng. Tuy nhi\u00ean, thay v\u00ec c\u00f3 m\u1ed9t c\u1ed9t cho m\u1ed7i th\u00e1ng, t\u1ed1t h\u01a1n n\u00ean c\u00f3 m\u1ed9t c\u1ed9t ch\u1ee9a t\u00ean th\u00e1ng l\u00e0m Attribute\u00a0 c\u00f9ng v\u1edbi c\u1ed9t gi\u00e1 tr\u1ecb \u0111\u01a1n l\u1ebb. \u0110\u00e2y l\u00e0 b\u1ed1 c\u1ee5c d\u1eef li\u1ec7u m\u00e0 ch\u00fang ta \u0111ang h\u01b0\u1edbng t\u1edbi \u1edf ph\u1ea7n cu\u1ed1i c\u1ee7a v\u00ed d\u1ee5 n\u00e0y.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ch\u1ecdn b\u1ea5t k\u1ef3 \u00f4 n\u00e0o trong ph\u1ea1m vi d\u1eef li\u1ec7u v\u00e0 thay \u0111\u1ed5i n\u00f3 th\u00e0nh B\u1ea3ng Excel b\u1eb1ng c\u00e1ch nh\u1ea5p v\u00e0o <\/span><b>Insert -&gt; Table<\/b><span style=\"font-weight: 400\"> (ho\u1eb7c nh\u1ea5n Ctrl + T)<\/span><\/p>\n<figure id=\"attachment_34186\" aria-describedby=\"caption-attachment-34186\" style=\"width: 797px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34186 size-full\" title=\"power-query-unpivot-du-lieu-2\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2-1-3.png\" alt=\"power-query-unpivot-du-lieu-2\" width=\"797\" height=\"136\"><figcaption id=\"caption-attachment-34186\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-2<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 <\/span><b>Create Table<\/b><span style=\"font-weight: 400\"> s\u1ebd \u0111t\u1ea1o v\u00f9ng d\u1eef li\u1ec7u v\u00e0 n\u1ebfu c\u00f3 h\u00e0ng ti\u00eau \u0111\u1ec1. Th\u00f4ng tin ngu\u1ed3n bao g\u1ed3m t\u1ed5ng s\u1ed1 trong h\u00e0ng 42, ch\u00fang ta c\u00f3 th\u1ec3 lo\u1ea1i tr\u1eeb th\u00f4ng tin n\u00e0y kh\u1ecfi v\u00f9ng d\u1eef d\u1eef li\u1ec7u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec d\u1eef li\u1ec7u c\u1ee7a ch\u00fang ta c\u00f3 m\u1ed9t h\u00e0ng ti\u00eau \u0111\u1ec1 duy nh\u1ea5t, ch\u00fang ta c\u00f3 th\u1ec3 ch\u1ecdn t\u00f9y ch\u1ecdn B\u1ea3ng c\u1ee7a t\u00f4i c\u00f3 ti\u00eau \u0111\u1ec1, sau \u0111\u00f3 nh\u1ea5p v\u00e0o OK.<\/span><\/p>\n<figure id=\"attachment_34187\" aria-describedby=\"caption-attachment-34187\" style=\"width: 286px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34187 size-full\" title=\"power-query-unpivot-du-lieu-3\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-1-4.png\" alt=\"power-query-unpivot-du-lieu-3\" width=\"286\" height=\"164\"><figcaption id=\"caption-attachment-34187\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-3<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">B\u1ea5m v\u00e0o b\u1ea5t k\u1ef3 \u00f4 n\u00e0o trong B\u1ea3ng, ribbon c\u1eeda s\u1ed5 <\/span><span style=\"font-weight: 400\">Table Design<\/span><span style=\"font-weight: 400\"> s\u1ebd xu\u1ea5t hi\u1ec7n. \u0110\u1eb7t cho B\u1ea3ng m\u1ed9t c\u00e1i t\u00ean (T\u00f4i \u0111\u00e3 s\u1eed d\u1ee5ng SalesData).<\/span><\/p>\n<figure id=\"attachment_34188\" aria-describedby=\"caption-attachment-34188\" style=\"width: 512px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34188 size-full\" title=\"power-query-unpivot-du-lieu-4\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/4-1-2.png\" alt=\"power-query-unpivot-du-lieu-4\" width=\"512\" height=\"86\"><figcaption id=\"caption-attachment-34188\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-4<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd ch\u00fang ta \u0111\u00e3 s\u1eb5n s\u00e0ng t\u1ea3i B\u1ea3ng SalesData v\u00e0o Power Query. B\u1ea5m <\/span><b>Data -&gt; From Table\/Range<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<figure id=\"attachment_34189\" aria-describedby=\"caption-attachment-34189\" style=\"width: 774px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34189 size-full\" title=\"power-query-unpivot-du-lieu-5\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/5-1-3.png\" alt=\"power-query-unpivot-du-lieu-5\" width=\"774\" height=\"138\"><figcaption id=\"caption-attachment-34189\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-5<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Power Query Editor m\u1edf ra hi\u1ec3n th\u1ecb b\u1ea3n xem tr\u01b0\u1edbc c\u1ee7a d\u1eef li\u1ec7u.<\/span><\/p>\n<figure id=\"attachment_34190\" aria-describedby=\"caption-attachment-34190\" style=\"width: 917px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34190 size-full\" title=\"power-query-unpivot-du-lieu-6\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/6-1-1.png\" alt=\"power-query-unpivot-du-lieu-6\" width=\"917\" height=\"297\"><figcaption id=\"caption-attachment-34190\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-6<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">N\u1ebfu t\u1ea1i b\u1ea5t k\u1ef3 th\u1eddi \u0111i\u1ec3m n\u00e0o trong t\u01b0\u01a1ng lai c\u00f3 th\u1ec3 c\u00f3 nhi\u1ec1u c\u1ed9t h\u01a1n ho\u1eb7c nhi\u1ec1u c\u1ed9t kh\u00e1c nhau, th\u00ec h\u00e3y x\u00f3a b\u01b0\u1edbc <\/span><b>Change Type<\/b><span style=\"font-weight: 400\">. B\u01b0\u1edbc n\u00e0y thay th\u1ebf c\u00e1c ti\u00eau \u0111\u1ec1 c\u1ed9t th\u00e0nh m\u00e3 M, do \u0111\u00f3 s\u1ebd kh\u00f4ng t\u1ef1 \u0111\u1ed9ng m\u1edf r\u1ed9ng ho\u1eb7c r\u00fat l\u1ea1i \u0111\u1ed1i v\u1edbi c\u00e1c b\u1ed1 c\u1ee5c c\u1ed9t kh\u00e1c nhau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">OK, b\u00e2y gi\u1edd cho vi\u1ec7c h\u1ee7y chia s\u1ebb di\u1ec5n ra. Trong n\u00fat <\/span><b>Transform -&gt; Unpivot Columns<\/b><span style=\"font-weight: 400\"> c\u00f3 ba t\u00f9y ch\u1ecdn, t\u00f9y ch\u1ecdn ch\u00fang ta ch\u1ecdn t\u00f9y thu\u1ed9c v\u00e0o k\u1ebft qu\u1ea3 ch\u00fang ta mu\u1ed1n.<\/span><\/p>\n<figure id=\"attachment_34191\" aria-describedby=\"caption-attachment-34191\" style=\"width: 512px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34191 size-full\" title=\"power-query-unpivot-du-lieu-7\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/7-1-1.png\" alt=\"power-query-unpivot-du-lieu-7\" width=\"512\" height=\"79\"><figcaption id=\"caption-attachment-34191\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-7<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">T\u1eeb ba l\u1ef1a ch\u1ecdn \u0111\u00f3, th\u1ef1c s\u1ef1 ch\u1ec9 c\u00f3 hai k\u1ebft qu\u1ea3; ch\u00fang ta c\u00f3 th\u1ec3 unpivot d\u1eef li\u1ec7u c\u1ee7a c\u00e1c c\u1ed9t \u0111\u00e3 ch\u1ecdn ho\u1eb7c kh\u00f4ng \u0111\u01b0\u1ee3c ch\u1ecdn. S\u1ef1 kh\u00e1c bi\u1ec7t l\u00e0 g\u00ec, v\u00e0 n\u00f3 c\u00f3 quan tr\u1ecdng kh\u00f4ng?<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu ch\u00fang ta ch\u1ecdn c\u1ed9t January, February v\u00e0 March v\u00e0 \u00e1p d\u1ee5ng <\/span><b>Unpivot Only Selected Columns<\/b><span style=\"font-weight: 400\">, th\u00ec n\u00f3 s\u1ebd kh\u00f4ng chia tr\u00ean c\u00e1c c\u1ed9t \u0111\u00f3. Power Query t\u1ea1o m\u1ed9t c\u1ed9t m\u1edbi c\u00f3 t\u00ean Attribute\u00a0 ch\u1ee9a c\u00e1c th\u00e1ng.<\/span><\/p>\n<figure id=\"attachment_34192\" aria-describedby=\"caption-attachment-34192\" style=\"width: 480px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34192 size-full\" title=\"power-query-unpivot-du-lieu-8\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/9-1-2.png\" alt=\"power-query-unpivot-du-lieu-8\" width=\"480\" height=\"329\"><figcaption id=\"caption-attachment-34192\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-8<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">M\u00e3 M \u0111\u1ec3 \u0111\u1ea1t \u0111\u01b0\u1ee3c s\u1ef1 chuy\u1ec3n \u0111\u1ed5i n\u00e0y s\u1ebd l\u00e0:<\/span><\/p>\n<p><span style=\"font-weight: 400\">= Table.Unpivot(#\u201dChanged Type\u201d, {\u201cJanuary\u201d, \u201cFebruary\u201d, \u201cMarch\u201d}, \u201cAttribute\u201d, \u201cValue\u201d)<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ti\u00eau \u0111\u1ec1 c\u1ed9t c\u1ee7a January, February v\u00e0 March \u0111\u01b0\u1ee3c thay th\u1ebf th\u00e0nh m\u00e3 M. V\u1eady \u0111i\u1ec1u g\u00ec s\u1ebd x\u1ea3y ra n\u1ebfu ch\u00fang ta th\u00eam d\u1eef li\u1ec7u b\u1ed5 sung cho April? Power Query s\u1ebd kh\u00f4ng unpivot d\u1eef li\u1ec7u c\u1ed9t April v\u00ec n\u00f3 kh\u00f4ng \u0111\u01b0\u1ee3c bao g\u1ed3m trong m\u00e3. Nh\u01b0ng n\u1ebfu b\u1ea1n c\u00f3 kh\u1ea3 n\u0103ng th\u00eam m\u1ed9t c\u1ed9t Attribute\u00a0 m\u1edbi th\u00ec n\u00f3 s\u1ebd ho\u1ea1t \u0111\u1ed9ng t\u1ed1t.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Thay v\u00e0o \u0111\u00f3, ch\u00fang ta c\u00f3 th\u1ec3 ch\u1ecdn c\u00e1c c\u1ed9t Customer v\u00e0 Product v\u00e0 s\u1eed d\u1ee5ng t\u00f9y ch\u1ecdn <\/span><b>Unpivot Other Columns<\/b><span style=\"font-weight: 400\">. \u0110\u1ed1i v\u1edbi t\u1eadp d\u1eef li\u1ec7u hi\u1ec7n t\u1ea1i c\u1ee7a ch\u00fang ta, n\u00f3 \u0111\u1ea1t \u0111\u01b0\u1ee3c c\u00f9ng m\u1ed9t k\u1ebft qu\u1ea3 tr\u1ef1c quan trong C\u1eeda s\u1ed5 xem tr\u01b0\u1edbc, nh\u01b0ng m\u00e3 M th\u00ec kh\u00e1c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">= Table.UnpivotOtherColumns(#\u201dChanged Type\u201d, {\u201cCustomer\u201d, \u201cProduct\u201d}, \u201cAttribute\u201d, \u201cValue\u201d)<\/span><\/p>\n<p><span style=\"font-weight: 400\">M\u00e3 kh\u00f4ng tham chi\u1ebfu \u0111\u1ebfn c\u00e1c c\u1ed9t th\u00e1ng; do \u0111\u00f3 b\u1ea5t k\u1ef3 th\u00e1ng n\u00e0o \u0111\u01b0\u1ee3c th\u00eam v\u00e0o c\u0169ng s\u1ebd kh\u00f4ng \u0111\u01b0\u1ee3c chia. \u0110i\u1ec1u n\u00e0y r\u1ea5t t\u1ed1t cho c\u00e1c c\u1ed9t d\u1eef li\u1ec7u, nh\u01b0ng kh\u00f4ng t\u1ed1t cho c\u00e1c c\u1ed9t Thu\u1ed9c t\u00ednh \/ k\u00edch th\u01b0\u1edbc.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd h\u00e3y thu g\u1ecdn truy v\u1ea5n v\u1edbi c\u00e1c ph\u00e9p bi\u1ebfn \u0111\u1ed5i sau:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Thay \u0111\u1ed5i ti\u00eau \u0111\u1ec1 c\u1ee7a c\u1ed9t Attribute\u00a0 th\u00e0nh Month.\u00a0\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Thay \u0111\u1ed5i ki\u1ec3u d\u1eef li\u1ec7u c\u1ee7a m\u1ed7i c\u1ed9t \u0111\u1ec3 ph\u00f9 h\u1ee3p v\u1edbi d\u1eef li\u1ec7u trong c\u1ed9t (T\u00f4i \u0111\u00e3 ch\u1ecdn V\u0103n b\u1ea3n, V\u0103n b\u1ea3n, V\u0103n b\u1ea3n, S\u1ed1 th\u1eadp ph\u00e2n).\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a0L\u1ecdc \u0111\u1ec3 lo\u1ea1i b\u1ecf c\u00e1c s\u1ed1 kh\u00f4ng kh\u1ecfi c\u1ed9t Value.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o Close &amp; Load \u0111\u1ec3 \u0111\u1ea9y d\u1eef li\u1ec7u v\u00e0o Excel. Trang t\u00ednh s\u1ebd gi\u1ed1ng nh\u01b0 sau:<\/span><\/p>\n<figure id=\"attachment_34193\" aria-describedby=\"caption-attachment-34193\" style=\"width: 817px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34193 size-full\" title=\"power-query-unpivot-du-lieu-10\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/10-1-1.png\" alt=\"power-query-unpivot-du-lieu-10\" width=\"817\" height=\"473\"><figcaption id=\"caption-attachment-34193\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-10<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">T\u1eeb \u0111\u00f3, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c ho\u1eb7c Pivot Table \u0111\u1ec3 t\u1ea1o b\u1ea5t k\u1ef3 d\u1ea1ng n\u00e0o ch\u00fang ta mu\u1ed1n.\u00a0 \u0110\u00e2y l\u00e0 s\u1ee9c m\u1ea1nh m\u00e0 vi\u1ec7c chu\u1ea9n b\u1ecb d\u1eef li\u1ec7u mang l\u1ea1i cho ch\u00fang ta.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Unpivot_du_lieu_voi_nhieu_cot\"><\/span><b>Unpivot d\u1eef li\u1ec7u v\u1edbi nhi\u1ec1u c\u1ed9t<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd l\u00e0 l\u00fac cho Example 2. D\u1eef li\u1ec7u ngu\u1ed3n tr\u00f4ng nh\u01b0 th\u1ebf n\u00e0y:<\/span><\/p>\n<figure id=\"attachment_34194\" aria-describedby=\"caption-attachment-34194\" style=\"width: 512px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34194 size-full\" title=\"power-query-unpivot-du-lieu-11\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/11-1-1.png\" alt=\"power-query-unpivot-du-lieu-11\" width=\"512\" height=\"160\"><figcaption id=\"caption-attachment-34194\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-11<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Thay v\u00ec m\u1ed9t c\u1ed9t cho m\u1ed7i th\u00e1ng, gi\u1edd \u0111\u00e2y ch\u00fang ta c\u00f3 hai c\u1ed9t, hi\u1ec3n th\u1ecb Sales Value v\u00e0 Sales Unit. \u0110\u1ecbnh d\u1ea1ng h\u1eefu \u00edch nh\u1ea5t l\u00e0 \u0111\u1eb7t t\u00ean th\u00e1ng trong m\u1ed9t c\u1ed9t v\u1edbi Value v\u00e0 Unit\u1ecb l\u00e0 c\u00e1c c\u1ed9t ri\u00eang bi\u1ec7t.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c b\u01b0\u1edbc b\u1eaft \u0111\u1ea7u gi\u1ed1ng nh\u01b0 Example 1:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Chuy\u1ec3n \u0111\u1ed5i d\u1eef li\u1ec7u th\u00e0nh B\u1ea3ng (s\u1eed d\u1ee5ng h\u00e0ng \u0111\u1ea7u ti\u00ean l\u00e0m ti\u00eau \u0111\u1ec1 v\u00e0 lo\u1ea1i tr\u1eeb t\u1ed5ng s\u1ed1 kh\u1ecfi v\u00f9ng d\u1eef li\u1ec7u).\u00a0\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u0110\u1eb7t t\u00ean cho b\u1ea3ng.\u00a0\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o <\/span><b>Data -&gt; From Table\/Range<\/b><span style=\"font-weight: 400\"> \u0111\u1ec3 t\u1ea3i d\u1eef li\u1ec7u v\u00e0o Power Query.\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u0110\u1ec3 an to\u00e0n, h\u00e3y x\u00f3a b\u01b0\u1edbc <\/span><span style=\"font-weight: 400\">Change Type<\/span><span style=\"font-weight: 400\"> \u0111\u01b0\u1ee3c t\u1ea1o b\u1edfi Power Query t\u1ef1 \u0111\u1ed9ng.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 xem tr\u01b0\u1edbc nh\u01b0 sau:<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd ch\u00fang ta c\u1ea7n m\u1ed9t chuy\u1ec3n \u0111\u1ed5i:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn c\u00e1c c\u1ed9t Customer v\u00e0 Product v\u00e0 nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Unpivot Columns (dropdown) -&gt; Unpivot Other Columns<\/b><span style=\"font-weight: 400\">.\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn c\u1ed9t <\/span><span style=\"font-weight: 400\">Attribute<\/span><span style=\"font-weight: 400\"> v\u00e0 chia c\u1ed9t th\u00e0nh c\u00e1c ph\u1ea7n ch\u00fang ta c\u1ea7n.\u00a0\u00a0<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">\u0110\u1ed1i v\u1edbi v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, ch\u00fang ta c\u1ea7n chia theo k\u00fd t\u1ef1 kho\u1ea3ng tr\u1eafng. B\u1ea5m <\/span><b>Transform -&gt; Split Column -&gt;\u00a0 Split by Delimeter<\/b><span style=\"font-weight: 400\">. Sau \u0111\u00f3, \u00e1p d\u1ee5ng c\u00e1c c\u00e0i \u0111\u1eb7t b\u00ean d\u01b0\u1edbi.<\/span><\/p>\n<figure id=\"attachment_34217\" aria-describedby=\"caption-attachment-34217\" style=\"width: 790px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34217 size-full\" title=\"power-query-unpivot-du-lieu-12\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/12-1-1.png\" alt=\"power-query-unpivot-du-lieu-12\" width=\"790\" height=\"379\"><figcaption id=\"caption-attachment-34217\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-12<\/figcaption><\/figure>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">C\u1ed9t Attribute b\u00e2y gi\u1edd s\u1ebd \u0111\u01b0\u1ee3c chia th\u00e0nh hai c\u1ed9t ri\u00eang bi\u1ec7t.\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn c\u1ed9t hi\u1ec3n th\u1ecb Value v\u00e0 Unit, sau \u0111\u00f3 b\u1ea5m <\/span><b>Transform -&gt; Pivot Column.<\/b><span style=\"font-weight: 400\">\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Trong c\u1eeda s\u1ed5 <\/span><span style=\"font-weight: 400\">Pivot Column<\/span><span style=\"font-weight: 400\">, ch\u1ecdn c\u1ed9t ch\u1ee9a c\u00e1c s\u1ed1, sau \u0111\u00f3 b\u1ea5m OK.<\/span><\/li>\n<li>\n<p><figure id=\"attachment_34218\" aria-describedby=\"caption-attachment-34218\" style=\"width: 512px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34218 size-full\" title=\"power-query-unpivot-du-lieu-13\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/13-1-1.png\" alt=\"power-query-unpivot-du-lieu-13\" width=\"512\" height=\"233\"><figcaption id=\"caption-attachment-34218\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-13<\/figcaption><\/figure>\n<\/p>\n<\/li>\n<\/ul>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Thay \u0111\u1ed5i t\u00ean c\u1ee7a c\u1ed9t Attribute th\u00e0nh Month.\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u0110\u1eb7t ki\u1ec3u d\u1eef li\u1ec7u cho m\u1ed7i c\u1ed9t.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Cu\u1ed1i c\u00f9ng nh\u1ea5p <\/span><b>Close &amp; Load.<\/b><\/p>\n<p><span style=\"font-weight: 400\">D\u1eef li\u1ec7u ch\u01b0a \u0111\u01b0\u1ee3c chia trong Excel b\u00e2y gi\u1edd tr\u00f4ng nh\u01b0 th\u1ebf n\u00e0y\u2026 ho\u00e0n h\u1ea3o!<\/span><\/p>\n<figure id=\"attachment_34219\" aria-describedby=\"caption-attachment-34219\" style=\"width: 542px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34219 size-full\" title=\"power-query-unpivot-du-lieu-14\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/14-1.png\" alt=\"power-query-unpivot-du-lieu-14\" width=\"542\" height=\"362\"><figcaption id=\"caption-attachment-34219\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-14<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Unpivot_voi_nhieu_hang_tieu_de\"><\/span><b>Unpivot v\u1edbi nhi\u1ec1u h\u00e0ng ti\u00eau \u0111\u1ec1<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd l\u00e0 v\u00ed d\u1ee5 cu\u1ed1i c\u00f9ng c\u1ee7a ch\u00fang ta. M\u1ecdi th\u1ee9 \u0111\u1ec1u gi\u1ed1ng nh\u01b0 Example 2, ngo\u1ea1i tr\u1eeb c\u00e1c ti\u00eau \u0111\u1ec1 b\u1ea3ng. B\u00e2y gi\u1edd ch\u00fang ta c\u00f3 hai h\u00e0ng ti\u00eau \u0111\u1ec1, h\u00e0ng \u0111\u1ea7u ti\u00ean hi\u1ec3n th\u1ecb th\u00e1ng v\u00e0 h\u00e0ng th\u1ee9 hai hi\u1ec3n th\u1ecb Value ho\u1eb7c Unit.<\/span><\/p>\n<figure id=\"attachment_34220\" aria-describedby=\"caption-attachment-34220\" style=\"width: 816px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34220 size-full\" title=\"power-query-unpivot-du-lieu-15\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/15-1.png\" alt=\"power-query-unpivot-du-lieu-15\" width=\"816\" height=\"236\"><figcaption id=\"caption-attachment-34220\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-15<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">B\u1ea3ng Excel v\u00e0 d\u1eef li\u1ec7u trong Power Query ch\u1ec9 c\u00f3 th\u1ec3 c\u00f3 m\u1ed9t h\u00e0ng ti\u00eau \u0111\u1ec1 duy nh\u1ea5t. \u0110\u00e2y l\u00e0 th\u1ef1c h\u00e0nh qu\u1ea3n l\u00fd d\u1eef li\u1ec7u t\u1ed1t. B\u1eb1ng c\u00e1ch t\u00e1ch ti\u00eau \u0111\u1ec1 th\u00e0nh hai h\u00e0ng, n\u00f3 tr\u1edf th\u00e0nh m\u1ed9t \u0111\u1ecbnh d\u1ea1ng tr\u00ecnh b\u00e0y r\u1ea5t kh\u00f3 l\u00e0m vi\u1ec7c trong Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e3y b\u1eaft \u0111\u1ea7u vi\u1ec7c unpivot d\u1eef li\u1ec7u b\u1eb1ng c\u00e1ch chuy\u1ec3n d\u1eef li\u1ec7u n\u00e0y th\u00e0nh B\u1ea3ng Excel. C\u00f3 m\u1ed9t \u0111i\u1ec3m kh\u00e1c bi\u1ec7t quan tr\u1ecdng \u0111\u1ed1i v\u1edbi c\u00e1c v\u00ed d\u1ee5 tr\u01b0\u1edbc \u0111\u00f3, kh\u00f4ng ch\u1ecdn t\u00f9y ch\u1ecdn MyTable c\u00f3 Headers.<\/span><\/p>\n<figure id=\"attachment_34221\" aria-describedby=\"caption-attachment-34221\" style=\"width: 286px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34221 size-full\" title=\"power-query-unpivot-du-lieu-16\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/16-1.png\" alt=\"power-query-unpivot-du-lieu-16\" width=\"286\" height=\"164\"><figcaption id=\"caption-attachment-34221\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-16<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Sau khi nh\u1ea5p v\u00e0o OK, M\u1ed9t s\u1ed1 thay \u0111\u1ed5i quan tr\u1ecdng s\u1ebd \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n \u0111\u1ed1i v\u1edbi d\u1eef li\u1ec7u ngu\u1ed3n.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">M\u1ed9t h\u00e0ng ti\u00eau \u0111\u1ec1 hi\u1ec3n th\u1ecb Column 1, Column 2, Column 3, v.v. \u0111\u00e3 \u0111\u01b0\u1ee3c th\u00eam\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">T\u1ea5t c\u1ea3 c\u00e1c h\u00e0ng \u0111\u00e3 \u0111\u01b0\u1ee3c di chuy\u1ec3n xu\u1ed1ng m\u1ed9t h\u00e0ng \u0111\u1ec3 t\u1ea1o kho\u1ea3ng tr\u1ed1ng cho h\u00e0ng ti\u00eau \u0111\u1ec1 m\u1edbi\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">C\u00e1c \u00f4 \u0111\u01b0\u1ee3c h\u1ee3p nh\u1ea5t \u0111\u00e3 x\u00f3a h\u1ee3p nh\u1ea5t, v\u1edbi n\u1ed9i dung \u0111\u01b0\u1ee3c \u0111\u1eb7t v\u00e0o \u00f4 \u0111\u1ea7u ti\u00ean c\u1ee7a m\u1ed7i\u00a0 khu v\u1ef1c h\u1ee3p nh\u1ea5t.<\/span><\/li>\n<li>\n<p><figure id=\"attachment_34222\" aria-describedby=\"caption-attachment-34222\" style=\"width: 876px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34222 size-full\" title=\"power-query-unpivot-du-lieu-17\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/17-1.png\" alt=\"power-query-unpivot-du-lieu-17\" width=\"876\" height=\"247\"><figcaption id=\"caption-attachment-34222\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-17<\/figcaption><\/figure>\n<\/p>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Nh\u1eadp d\u1eef li\u1ec7u v\u00e0o Power Query b\u1eb1ng c\u00e1ch ch\u1ecdn b\u1ea5t k\u1ef3 \u00f4 n\u00e0o trong B\u1ea3ng, sau \u0111\u00f3 nh\u1ea5p v\u00e0o <\/span><b>Data -&gt; From Table\/Range<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 xem tr\u01b0\u1edbc hi\u1ec3n th\u1ecb nh\u01b0 sau:<\/span><\/p>\n<figure id=\"attachment_34223\" aria-describedby=\"caption-attachment-34223\" style=\"width: 914px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34223 size-full\" title=\"power-query-unpivot-du-lieu-18\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/18-1.png\" alt=\"power-query-unpivot-du-lieu-18\" width=\"914\" height=\"234\"><figcaption id=\"caption-attachment-34223\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-18<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Quan \u0111i\u1ec3m n\u00e0y cho ch\u00fang ta m\u1ed9t v\u1ea5n \u0111\u1ec1; n\u00f3 kh\u00f4ng hi\u1ec3n th\u1ecb th\u00e1ng trong m\u1ed7i c\u1ed9t. N\u1ebfu ch\u00fang ta c\u1ed1 g\u1eafng unpivot d\u1eef li\u1ec7u, n\u00f3 s\u1ebd unpivot d\u1eef li\u1ec7u v\u1edbi gi\u00e1 tr\u1ecb null. \u0110\u1ea7u ti\u00ean ch\u00fang ta c\u1ea7n s\u1eed d\u1ee5ng ph\u00e9p chuy\u1ec3n \u0111\u1ed5i Fill Across\u2026 nh\u01b0ng kh\u00f4ng c\u00f3! Thay v\u00e0o \u0111\u00f3, ch\u00fang ta c\u1ea7n chuy\u1ec3n \u0111\u1ed5i d\u1eef li\u1ec7u, Fill Down, th\u1ef1c hi\u1ec7n m\u1ed9t s\u1ed1 ph\u00e9p bi\u1ebfn \u0111\u1ed5i, sau \u0111\u00f3 chuy\u1ec3n \u0111\u1ed5i l\u1ea1i m\u1ed9t l\u1ea7n n\u1eefa.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Transpose<\/b><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn Column 1, nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Fill (drop-down) -&gt; Down<\/b><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn Column 1 v\u00e0 Column 2\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Merge Columns<\/b><span style=\"font-weight: 400\">\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Trong c\u1eeda s\u1ed5 <\/span><span style=\"font-weight: 400\">Merge Columns<\/span><span style=\"font-weight: 400\">, ch\u1ecdn d\u1ea5u ph\u00e2n t\u00e1ch kh\u00f4ng c\u00f3 trong v\u0103n b\u1ea3n c\u1ee7a hai c\u1ed9t.\u00a0 (T\u00f4i \u0111\u00e3 t\u00ecm m\u1ed9t kho\u1ea3ng tr\u1ed1ng, nh\u01b0ng \u0111\u00e2y c\u00f3 th\u1ec3 kh\u00f4ng ph\u1ea3i l\u00e0 l\u1ef1a ch\u1ecdn t\u1ed1t nh\u1ea5t cho tr\u01b0\u1eddng h\u1ee3p c\u1ee7a b\u1ea1n)\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Transpos<\/b><span style=\"font-weight: 400\"> \u0111\u1ec3 \u0111\u01b0a d\u1eef li\u1ec7u tr\u1edf l\u1ea1i \u0111\u1ecbnh d\u1ea1ng tr\u01b0\u1edbc \u0111\u00f3 Ch\u1ecdn Column 1 v\u00e0 Column 2.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Format (dropdown) -&gt; Trim<\/b><span style=\"font-weight: 400\"> \u0111\u1ec3 x\u00f3a k\u00fd t\u1ef1 kho\u1ea3ng tr\u1eafng kh\u1ecfi c\u00e1c c\u1ed9t Customer v\u00e0 Product m\u00e0 ch\u00fang t\u00f4i \u0111\u00e3 th\u00eam \u1edf b\u01b0\u1edbc tr\u01b0\u1edbc\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">B\u00e2y gi\u1edd h\u00e3y \u0111\u01b0a h\u00e0ng \u0111\u1ea7u ti\u00ean l\u00e0m ti\u00eau \u0111\u1ec1 b\u1eb1ng c\u00e1ch nh\u1ea5p v\u00e0o<\/span><b>Transform -&gt; Use First Row as Headers<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Sau t\u1ea5t c\u1ea3 nh\u1eefng bi\u1ebfn \u0111\u1ed5i \u0111\u00f3, C\u1eeda s\u1ed5 Xem tr\u01b0\u1edbc s\u1ebd tr\u00f4ng nh\u01b0 th\u1ebf n\u00e0y:<\/span><\/p>\n<figure id=\"attachment_34224\" aria-describedby=\"caption-attachment-34224\" style=\"width: 1024px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-34224 size-full\" title=\"power-query-unpivot-du-lieu-19\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/19-1.png\" alt=\"power-query-unpivot-du-lieu-19\" width=\"1024\" height=\"209\"><figcaption id=\"caption-attachment-34224\" class=\"wp-caption-text\">power-query-unpivot-du-lieu-19<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd n\u00f3 c\u00f3 \u0111\u1ecbnh d\u1ea1ng gi\u1ed1ng nh\u01b0 ch\u00fang ta \u0111\u00e3 c\u00f3 trong Example 2. \u0110\u1ec3 ho\u00e0n th\u00e0nh v\u00ed d\u1ee5 n\u00e0y, h\u00e3y l\u00e0m theo t\u1ea5t c\u1ea3 c\u00e1c b\u01b0\u1edbc chuy\u1ec3n \u0111\u1ed5i t\u1eeb v\u00ed d\u1ee5 \u0111\u00f3.<\/span><\/p>\n<p><em>Ch\u1eafc h\u1eb3n qua b\u00e0i vi\u1ebft n\u00e0y c\u00e1c b\u1ea1n \u0111\u00e3 c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng <span style=\"font-weight: 400\"><strong>unpivot d\u1eef li\u1ec7u <\/strong><\/span>v\u1edbi power query r\u1ed3i. <strong>H\u1ecdc Excel Online<\/strong> ch\u00fac c\u00e1c b\u1ea1n lu\u00f4n th\u00e0nh c\u00f4ng trong vi\u1ec7c h\u1ecdc v\u00e0 \u1ee9ng d\u1ee5ng Excel c\u1ee7a m\u00ecnh.\u00a0<\/em><\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-282.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":3645,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-3644","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3644","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=3644"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3644\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/3645"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=3644"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=3644"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=3644"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}