{"id":4171,"date":"2024-12-04T12:31:03","date_gmt":"2024-12-04T12:31:03","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/04\/huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu\/"},"modified":"2024-12-04T12:31:03","modified_gmt":"2024-12-04T12:31:03","slug":"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/04\/huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng Power Query \u0111\u1ec3 chu\u1ea9n ho\u00e1 d\u1eef li\u1ec7u"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-Bh-YYrla#1\">\n<p style=\"text-align: justify\"><em>Trong b\u00e0i vi\u1ebft n\u00e0y, <strong><a href=\"http:\/\/blog.ezworkapp.com\">H\u1ecdc Excel Online<\/a><\/strong> s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1ch chu\u1ea9n ho\u00e1 d\u1eef li\u1ec7u, n\u00f3i c\u00e1ch kh\u00e1c l\u00e0 \u0111\u01b0a d\u1eef li\u1ec7u v\u1ec1 d\u1ea1ng \u201cChu\u1ea9n\u201d v\u1edbi c\u00f4ng c\u1ee5 Power Query trong Excel.<\/em><\/p>\n<p style=\"text-align: justify\">Chu\u1ea9n b\u1ecb d\u1eef li\u1ec7u t\u1ed1t c\u00f3 l\u1ebd l\u00e0 y\u1ebfu t\u1ed1 th\u00e0nh c\u00f4ng quan tr\u1ecdng nh\u1ea5t trong ph\u00e2n t\u00edch d\u1eef li\u1ec7u. N\u1ebfu b\u1ed1 c\u1ee5c d\u1eef li\u1ec7u kh\u00f4ng \u0111\u00fang, b\u1ea1n s\u1ebd ti\u1ebfp t\u1ee5c chi\u1ebfn \u0111\u1ea5u v\u1edbi Excel; ph\u1ea3i s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng th\u1ee9c Excel n\u00e2ng cao, r\u1ea5t ph\u1ee9c t\u1ea1p; th\u00eam c\u00e1c c\u1ed9t kh\u00f4ng c\u1ea7n thi\u1ebft ho\u1eb7c th\u1eadm ch\u00ed sao ch\u00e9p d\u1eef li\u1ec7u,\u2026.<\/p>\n<p style=\"text-align: justify\">Nh\u01b0ng n\u1ebfu d\u1eef li\u1ec7u n\u1eb1m trong c\u1ea5u tr\u00fac ph\u00f9 h\u1ee3p, Excel s\u1ebd tr\u1edf n\u00ean d\u1ec5 d\u00e0ng. H\u00e3y c\u00f9ng xem <strong>Power Query<\/strong> s\u1ebd gi\u00fap g\u00ec cho ch\u00fang ta trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y qua m\u1ed9t s\u1ed1 v\u00ed d\u1ee5 trong b\u00e0i vi\u1ebft n\u00e0y c\u1ee7a <strong>H\u1ecdc Excel Online.<\/strong><\/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-67504b868668b\" 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\/huong-dan-cach-dua-du-lieu-ve-dang-chuan-voi-power-query-trong-excel.html#Vi_du_1\" title=\"V\u00ed d\u1ee5 1\">V\u00ed d\u1ee5 1<\/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\/huong-dan-cach-dua-du-lieu-ve-dang-chuan-voi-power-query-trong-excel.html#Vi_du_2\" title=\"V\u00ed d\u1ee5 2\">V\u00ed d\u1ee5 2<\/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\/huong-dan-cach-dua-du-lieu-ve-dang-chuan-voi-power-query-trong-excel.html#Vi_du_3\" title=\"V\u00ed d\u1ee5 3\">V\u00ed d\u1ee5 3<\/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\/huong-dan-cach-dua-du-lieu-ve-dang-chuan-voi-power-query-trong-excel.html#Mot_vai_luu_y\" title=\"M\u1ed9t v\u00e0i l\u01b0u \u00fd\">M\u1ed9t v\u00e0i l\u01b0u \u00fd<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-dua-du-lieu-ve-dang-chuan-voi-power-query-trong-excel.html#Ket_luan\" title=\"K\u1ebft lu\u1eadn\">K\u1ebft lu\u1eadn<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_1\"><\/span>V\u00ed d\u1ee5 1<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">\u1ede \u0111\u00e2y, m\u00ecnh c\u00f3 b\u1ea3ng d\u1eef li\u1ec7u nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30281 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-14.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-1\" width=\"712\" height=\"580\" title=\"1-14\"><\/p>\n<p style=\"text-align: justify\">B\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y, d\u1eef li\u1ec7u\u00a0g\u1ed1c\u00a0hi\u1ec3n th\u1ecb m\u1ed9t c\u1ed9t ri\u00eang cho m\u1ed7i qu\u00fd.\u00a0Tuy nhi\u00ean, thay v\u00ec c\u00f3 m\u1ed9t c\u1ed9t cho m\u1ed7i qu\u00fd th\u00ec\u00a0t\u1ea1i sao ta kh\u00f4ng \u0111\u01b0a c\u00e1c qu\u00fd \u0111\u00f3 v\u00e0o 1 c\u1ed9t ri\u00eang v\u00e0 \u0111\u1eb7t t\u00ean ti\u00eau \u0111\u1ec1 c\u1ed9t l\u00e0 Qu\u00fd.\u00a0\u0110\u00e2y l\u00e0 c\u00e1ch b\u1ed1 tr\u00ed d\u1eef li\u1ec7u m\u00e0 H\u1ecdc Excel Online \u0111ang c\u1ed1 th\u1ef1c hi\u1ec7n trong b\u00e0i vi\u1ebft n\u00e0y \u2013 M\u1ed9t c\u00e1ch b\u1ed1 tr\u00ed d\u1eef li\u1ec7u \u201cChu\u1ea9n\u201d.<\/p>\n<p style=\"text-align: justify\">V\u1eady l\u00e0m nh\u01b0 th\u1ebf n\u00e0o \u0111\u1ec3 ta c\u00f3 th\u1ec3 thi\u1ebft k\u1ebf l\u1ea1i d\u1eef li\u1ec7u nh\u01b0 \u0111\u00e3 m\u00f4 t\u1ea3 tr\u00ean?<\/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-333.gif\" title=\"ex101_92019-nho-333\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p style=\"text-align: justify\">B\u1ea1n s\u1ebd l\u00e0m \u0111i\u1ec1u \u0111\u00f3 trong Excel nh\u01b0 th\u1ebf n\u00e0o?. B\u1ea1n s\u1ebd Copy v\u00e0 Paste m\u1ed9t c\u00e1ch th\u1ee7 c\u00f4ng v\u00e0 r\u1ea5t d\u1ec5 sai s\u00f3t, nh\u1ea7m l\u1eabn,\u2026v\u00e0 m\u1ed7i l\u1ea7n d\u1eef li\u1ec7u g\u1ed1c thay \u0111\u1ed5i b\u1ea1n l\u1ea1i Copy l\u1ea1i???. Hay l\u00e0 b\u1ea1n s\u1ebd x\u1eed l\u00fd n\u00f3 v\u1edbi k\u1ef9 n\u0103ng VBA m\u00e0 m\u00ecnh c\u00f3???<\/p>\n<p style=\"text-align: justify\"><strong>KH\u00d4NG<\/strong>, Kh\u00f4ng n\u00ean m\u1ed9t ch\u00fat n\u00e0o n\u1ebfu nh\u01b0 b\u1ea1n \u0111\u00e3 c\u00f3 c\u00f4ng c\u1ee5 Power Query th\u00ec h\u00e3y \u0111\u1ec3 n\u00f3 x\u1eed l\u00fd vi\u1ec7c \u0111\u00f3 cho b\u1ea1n m\u1ed9t c\u00e1ch r\u1ea5t nhanh ch\u00f3ng v\u00e0 hi\u1ec7u qu\u1ea3.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ea7u ti\u00ean b\u1ea1n n\u00ean \u0111\u01b0a d\u1eef li\u1ec7u tr\u00ean v\u1ec1 d\u1ea1ng Table (Ctrl + T or Ctrl + L) v\u00e0 \u0111\u1eb7t t\u00ean cho Table n\u00f3 1 c\u00e1i t\u00ean (\u1edf \u0111\u00e2y m\u00ecnh \u0111\u00e3 \u0111\u1eb7t t\u00ean l\u00e0 VD_1).\u00a0Power Query h\u1ed7 tr\u1ee3 cho b\u1ea1n kh\u00e1 nhi\u1ec1u ki\u1ec3u \u0111\u1ec3 Input d\u1eef li\u1ec7u v\u00e0o nh\u01b0 Table, Name range (Define name), Name \u0111\u1ed9ng hay th\u1eadm ch\u00ed b\u1ea1n ch\u1ec9 c\u1ea7n qu\u00e9t ch\u1ecdn 1 v\u00f9ng th\u00ec Power Query s\u1ebd nh\u1eadn v\u00f9ng \u0111\u00f3 \u0111\u1ec3 \u0111\u01b0a v\u00e0o Power Query Editor. Tuy nhi\u00ean, v\u1edbi d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c l\u1ea5y t\u1eeb Excel th\u00ec t\u1ed1t nh\u1ea5t l\u00e0 b\u1ea1n n\u00ean t\u1ea1o Table cho n\u00f3.<\/p>\n<p style=\"text-align: justify\">Ti\u1ebfp theo, b\u1ea1n\u00a0ch\u1ec9 c\u1ea7n ch\u1ecdn 1 \u00f4 b\u1ea5t k\u1ef3 trong b\u1ea3ng \u0111\u00f3 v\u00e0o ch\u1ecdn From Table\/Range<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30282 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2-12.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-2\" width=\"807\" height=\"835\" title=\"2-12\"><\/p>\n<p style=\"text-align: justify\">L\u00fac n\u00e0y, C\u1eeda s\u1ed5 Power Query\u00a0Editor s\u1ebd m\u1edf l\u00ean<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30283 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-1024x430-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-3\" width=\"1024\" height=\"430\" title=\"3-1024x430-1\"><\/p>\n<p style=\"text-align: justify\">\u1ede m\u1ee5c Applied Steps, b\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y c\u00f3 2 b\u01b0\u1edbc:<\/p>\n<ul style=\"text-align: justify\">\n<li><strong>Source:<\/strong> \u0110\u00e2y l\u00e0 ngu\u1ed3n d\u1eef li\u1ec7u m\u00e0 b\u1ea1n Input v\u00e0o\u00a0Power Query (trong b\u00e0i vi\u1ebft n\u00e0y l\u00e0 t\u1eeb Excel)<\/li>\n<li><strong>Changed Type: <\/strong>\u0110\u00e2y l\u00e0 b\u01b0\u1edbc m\u00e0 Power Query t\u1ef1 nh\u1eadn ki\u1ec3u \u0111\u1ecbnh d\u1ea1ng cho m\u1ed7i tr\u01b0\u1eddng th\u00f4ng tin (c\u1ed9t) khi n\u00e0o Input d\u1eef li\u1ec7u v\u00e0o. N\u1ebfu b\u1ea1n kh\u00f4ng mu\u1ed1n Power Query Editor t\u1ef1 \u0111\u1ed9ng thay \u0111\u1ed5i \u0111\u1ecbnh d\u1ea1ng nh\u01b0 v\u1eady th\u00ec b\u1ea1n c\u00f3 th\u1ec3 b\u1ecf b\u01b0\u1edbc n\u00e0y b\u1eb1ng c\u00e1ch \u1ea5n v\u00e0o d\u1ea5u X ph\u00eda tr\u01b0\u1edbc b\u01b0\u1edbc \u0111\u00f3. M\u00ecnh s\u1ebd l\u00e0m \u0111i\u1ec1u \u0111\u00f3.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Quay l\u1ea1i v\u1edbi m\u1ee5c \u0111\u00edch ban \u0111\u1ea7u l\u00e0 \u0111\u01b0a d\u1eef li\u1ec7u v\u1ec1 d\u1ea1ng \u201cChu\u1ea9n\u201d. T\u1ea1i c\u1eeda s\u1ed5 Power Query Editor b\u1ea1n v\u00e0o Tab Transform v\u00e0 \u0111\u1ec3 \u00fd t\u1edbi m\u1ee5c Unpivot Columns (ta s\u1ebd s\u1eed d\u1ee5ng n\u00f3 xuy\u00ean su\u1ed1t b\u00e0i vi\u1ebft n\u00e0y).<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30284 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/4-1024x168-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-4\" width=\"1024\" height=\"168\" title=\"4-1024x168-1\"><\/p>\n<p style=\"text-align: justify\">Ta s\u1ebd c\u00f3 2 c\u00e1ch l\u00e0m nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>C\u00e1ch 1: S\u1eed d\u1ee5ng Unpivot Columns ho\u1eb7c Unpivot Only Selected Columns<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">V\u1edbi c\u00e1ch n\u00e0y, b\u1ea1n ch\u1ecdn c\u00e1c c\u1ed9t Qu\u00fd 1, Qu\u00fd 2,\u00a0Qu\u00fd 3, Qu\u00fd\u00a04. Sau \u0111\u00f3 th\u1ef1c hi\u1ec7n Unpivot Columns ho\u1eb7c\u00a0Unpivot Only Selected Columns.<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30285 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/5-1024x431-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-5\" width=\"1024\" height=\"431\" title=\"5-1024x431-1\"><\/p>\n<p style=\"text-align: justify\">\u1ede \u0111\u00e2y m\u00ecnh l\u1ef1a ch\u1ecdn\u00a0Unpivot Columns. Sau \u0111\u00f3 b\u1ea1n c\u00f3 th\u1ec3 \u0111\u1ed5i t\u00ean c\u1ed9t <strong>Attribute<\/strong> th\u00e0nh <strong>Qu\u00fd<\/strong>. Power Query s\u1ebd th\u1ef1c hi\u1ec7n v\u00e0 ghi l\u1ea1i b\u01b0\u1edbc \u0111\u00f3 trong m\u1ee5c Applied Steps v\u00e0 b\u1ea1n c\u00f3 k\u1ebft qu\u1ea3 sau khi th\u1ef1c hi\u1ec7n nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/6-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30287 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/6-1-1024x360-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-6\" width=\"1024\" height=\"360\" title=\"6-1-1024x360-1\"><\/a><\/p>\n<p style=\"text-align: justify\">Nh\u01b0 v\u1eady l\u00e0 xong r\u1ed3i, th\u1eadt \u0111\u01a1n gi\u1ea3n v\u00e0 nhanh ch\u00f3ng ph\u1ea3i kh\u00f4ng n\u00e0o .<\/p>\n<p style=\"text-align: justify\">Gi\u1edd b\u1ea1n ch\u1ec9 c\u1ea7n quay l\u1ea1i Tab Home ch\u1ecdn Close &amp; Load v\u00e0o Excel th\u00f4i l\u00e0 b\u1ea1n \u0111\u00e3 c\u00f3 th\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c 1 b\u1ea3ng d\u1eef li\u1ec7u \u0111\u00fang \u201cChu\u1ea9n\u201d r\u1ed3i. \u1ede \u0111\u00e2y, m\u00ecnh ch\u1ecdc Close &amp; Load To\u2026. v\u00e0 ch\u1ecdn\u00a0c\u00e1ch xem d\u1eef li\u1ec7u tr\u1ea3 v\u1ec1 l\u00e0 Table v\u00e0 \u0111\u1eb7t t\u1ea1i \u00f4 I3 trong Sheet VD_1.<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/7.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30288 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/7-8.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-7\" width=\"428\" height=\"431\" title=\"7-8\"><\/a><\/p>\n<p style=\"text-align: justify\">K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 1 Table v\u1edbi d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c chuy\u1ec3n \u0111\u1ed5i v\u1ec1 \u0111\u00fang \u201cChu\u1ea9n\u201d v\u1edbi c\u00e1c tr\u01b0\u1eddng th\u00f4ng tin (c\u1ed9t) nh\u01b0 h\u00ecnh d\u01b0\u1edbi.<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/8.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30289 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/8-1024x576-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-8\" width=\"1024\" height=\"576\" title=\"8-1024x576-1\"><\/a><\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>C\u00e1ch 2: S\u1eed d\u1ee5ng Unpivot Other Columns<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">V\u1edbi c\u00e1ch n\u00e0y. Thay v\u00ec l\u1ef1a ch\u1ecdn 4 c\u1ed9t Qu\u00fd 1, Qu\u00fd 2, Qu\u00fd 3, Qu\u00fd 4 nh\u01b0 c\u00e1ch 1 th\u00ec b\u1ea1n s\u1ebd l\u1ef1a ch\u1ecdn c\u00e1c c\u1ed9t c\u00f2n l\u1ea1i trong b\u1ea3ng \u0111\u00f3 l\u00e0 c\u1ed9t STT v\u00e0 c\u1ed9t H\u00e0ng ho\u00e1. V\u00e0o Transform v\u00e0 l\u1ef1a ch\u1ecdn Unpivot Other Columns<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/9.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30290 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/9-1024x434-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-9\" width=\"1024\" height=\"434\" title=\"9-1024x434-1\"><\/a><\/p>\n<p style=\"text-align: justify\">B\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y,\u00a0k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 c\u0169ng s\u1ebd gi\u1ed1ng nh\u01b0 c\u00e1ch 1<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30291 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/10-1024x334-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-10\" width=\"1024\" height=\"334\" title=\"10-1024x334-1\"><\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>V\u1eady, c\u00e2u h\u1ecfi \u0111\u1eb7t ra l\u00e0 s\u1ef1 kh\u00e1c bi\u1ec7t gi\u1eefa 2 c\u00e1ch n\u00e0y l\u00e0 g\u00ec?<\/strong><\/span><\/p>\n<p style=\"text-align: justify\"><strong>S\u1ef1 kh\u00e1c bi\u1ec7t \u1edf \u0111\u00e2y l\u00e0:<\/strong> N\u1ebfu b\u1ea1n l\u00e0m theo c\u00e1ch 1 th\u00ec gi\u1ea3 s\u1eed khi b\u1ea1n c\u00f3 th\u00eam 1 ho\u1eb7c nhi\u1ec1u c\u1ed9t n\u1eefa v\u00e0o d\u1eef li\u1ec7u g\u1ed1c (VD: th\u00eam c\u1ed9t Qu\u00fd 5, Qu\u00fd 6,\u2026.) th\u00ec khi b\u1ea1n c\u1eadp nh\u1eadt, Power Query s\u1ebd kh\u00f4ng t\u1ef1 hi\u1ec3u \u0111\u1ec3 Unpivot cho nh\u1eefng Columns qu\u00fd m\u00e0 b\u1ea1n th\u00eam m\u1edbi \u0111\u00f3.<\/p>\n<p style=\"text-align: justify\">C\u00f2n v\u1edbi c\u00e1ch l\u00e0m th\u1ee9 2 l\u1ef1a ch\u1ecdn\u00a0Unpivot Other Columns s\u1ebd kh\u1eafc ph\u1ee5c \u0111\u01b0\u1ee3c \u0111i\u1ec1u \u0111\u00f3. Tuy nhi\u00ean, b\u1ea1n c\u0169ng c\u1ea7n hi\u1ec3u r\u00f5 b\u1ea3n ch\u1ea5t h\u01a1n n\u1eefa \u0111\u1ec3 l\u1ef1a ch\u1ecdn c\u00e1ch Unpivot cho ph\u00f9 h\u1ee3p.<\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_2\"><\/span>V\u00ed d\u1ee5 2<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">\u1ede v\u00ed d\u1ee5 n\u00e0y, b\u1ea3ng d\u1eef li\u1ec7u g\u1ed1c l\u1ea1i \u0111\u01b0\u1ee3c c\u1ea5u tr\u00fac nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/11.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30292 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/11-3.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-11\" width=\"712\" height=\"580\" title=\"11-3\"><\/a><\/p>\n<p style=\"text-align: justify\">V\u1edbi t\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u nh\u01b0 n\u00e0y, th\u1eadm ch\u00ed c\u00f2n t\u1ed3i t\u1ec7 h\u01a1n v\u00ed d\u1ee5 1 tr\u00ean . \u1ede \u0111\u00e2y \u0111\u00e1ng ra n\u00ean t\u1ed5 ch\u1ee9c th\u00e0nh c\u00e1c c\u1ed9t l\u00e0 <strong>STT, H\u00e0ng ho\u00e1, Th\u00e1ng, Doanh thu (DT), Chi ph\u00ed (CP)<\/strong> th\u00ec c\u00f3 v\u1ebb h\u1ee3p l\u00fd h\u01a1n ph\u1ea3i kh\u00f4ng n\u00e0o. V\u1eady, l\u00e0m \u0111i\u1ec1u \u0111\u00f3 nh\u01b0 th\u1ebf n\u00e0o \u0111\u00e2y???. Copy, Paste hay VBA \u0111\u00e2y???. H\u00e3y qu\u00ean nh\u1eefng c\u00e1ch \u0111\u00f3 \u0111i v\u00ec \u0111\u00e3 c\u00f3 Power Query.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ea7u ti\u1ec1n, b\u1ea1n c\u1ea7n t\u1ea1o 1 Table v\u00e0 \u0111\u1eb7t cho n\u00f3 1 c\u00e1i t\u00ean (M\u00ecnh \u0111\u00e3 \u0111\u1eb7t VD_2 l\u00e0 t\u00ean c\u1ee7a Table n\u00e0y). C\u0169ng v\u1edbi c\u00e1c b\u01b0\u1edbc nh\u01b0 v\u00ed d\u1ee5 1, ta th\u1ef1c hi\u1ec7n Input d\u1eef li\u1ec7u v\u00e0o Power Query Editor. V\u1eabn v\u1edbi Unpivot, v\u00e0 v\u1ea5n \u0111\u1ec1 l\u00e0 ta s\u1ebd Unpivot nh\u01b0 n\u00e0o \u0111\u1ec3 d\u1eef li\u1ec7u n\u00e0y ra \u0111\u01b0\u1ee3c d\u1ea1ng \u201cChu\u1ea9n\u201d ?.<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/12.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30294 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/12-1024x431-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-12\" width=\"1024\" height=\"431\" title=\"12-1024x431-1\"><\/a><\/p>\n<p style=\"text-align: justify\">Ta s\u1ebd th\u1ef1c hi\u1ec7n \u0111i\u1ec1u \u0111\u00f3 v\u1edbi c\u00e1c b\u01b0\u1edbc sau:<\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>B\u01b0\u1edbc 1: Th\u1ef1c hi\u1ec7n Unpivot<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">Ch\u1ecdn Unpivot Other Columns, sau b\u01b0\u1edbc n\u00e0y ta \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/13.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30296 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/13-1024x345-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-13\" width=\"1024\" height=\"345\" title=\"13-1024x345-1\"><\/a><\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>B\u01b0\u1edbc 2: T\u00e1ch c\u1ed9t Attribute th\u00e0nh 2 c\u1ed9t<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">Ta th\u1ea5y, nh\u1eefng k\u00fd t\u1ef1 b\u00ean ph\u1ea3i d\u1ea5u _ n\u00f3 ch\u1ec9nh l\u00e0 th\u00e1ng. Ta s\u1ebd th\u1ef1c hi\u1ec7n t\u00e1ch c\u1ed9t\u00a0Attribute\u00a0 th\u00e0nh 2 c\u1ed9t, b\u1eb1ng t\u00ednh n\u0103ng Split Columns v\u1edbi delimiter l\u00e0 d\u1ea5u _<\/p>\n<p style=\"text-align: justify\">Trong tr\u01b0\u1eddng h\u1ee3p\u00a0delimiter\u00a0kh\u00f4ng c\u00f3 th\u00ec b\u1ea1n c\u00f3 th\u1ec3 t\u1ea1o ra b\u1eb1ng c\u00e1ch s\u1eeda t\u00ean c\u1ed9t cho ph\u00f9 h\u1ee3p, \u0111\u1ec3 \u0111\u00e1p \u1ee9ng \u0111\u01b0\u1ee3c cho vi\u1ec7c x\u1eed l\u00fd d\u1eef li\u1ec7u.<\/p>\n<p style=\"text-align: justify\">K\u1ebft qu\u1ea3 sau khi th\u1ef1c hi\u1ec7n b\u01b0\u1edbc n\u00e0y b\u1ea1n s\u1ebd \u0111\u01b0\u1ee3c nh\u01b0 sau, l\u00fac c\u1ed9t\u00a0Attribute.2 n\u00f3 ch\u00ednh l\u00e0 c\u1ed9t Th\u00e1ng m\u00e0 ta c\u1ea7n.<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/15.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30298 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/15-1024x364-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-14\" width=\"1024\" height=\"364\" title=\"15-1024x364-1\"><\/a><\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>B\u01b0\u1edbc 3: Pivot c\u1ed9t\u00a0Attribute.1<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">C\u1ed9t\u00a0Attribute.1 \u0111ang ch\u1ee9a c\u1ea3 DT (doanh thu) v\u00e0 CP (chi ph\u00ed), \u0111\u00fang ra th\u00ec n\u00f3 ph\u1ea3i l\u00e0 2 c\u1ed9t ph\u1ea3i kh\u00f4ng, 1 c\u1ed9t l\u00e0 DT v\u00e0 1 c\u1ed9t l\u00e0 CP.<\/p>\n<p style=\"text-align: justify\">B\u1ea1n ch\u1ecdn c\u1ed9t\u00a0Attribute.1 v\u00e0 c\u1ed9t Value \u0111\u1ec3 th\u1ef1c hi\u1ec7n Pivot. K\u1ebft qu\u1ea3 sau b\u01b0\u1edbc n\u00e0y b\u1ea1n s\u1ebd c\u00f3 b\u1ea3ng d\u1eef li\u1ec7u \u0111\u00fang \u201cChu\u1ea9n\u201d nh\u01b0 h\u00ecnh<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/16.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30299 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/16-1024x388-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-15\" width=\"1024\" height=\"388\" title=\"16-1024x388-1\"><\/a><\/p>\n<p style=\"text-align: justify\">Nh\u00ecn d\u1eef li\u1ec7u sau khi chuy\u1ec3n \u0111\u1ed5i c\u00f3 v\u1ebb kh\u1ea3 quan v\u00e0 h\u1ee3p l\u00fd h\u01a1n ban \u0111\u1ea7u ph\u1ea3i kh\u00f4ng n\u00e0o. B\u00e2y gi\u1edd b\u1ea1n ch\u1ec9 c\u1ea7n th\u1ef1c hi\u1ec7n m\u1ed9t s\u1ed1 thao t\u00e1c \u0111\u01a1n gi\u1ea3n nh\u01b0 \u0111\u1ed5i t\u00ean ti\u00eau \u0111\u1ec1 c\u00e1c c\u1ed9t, \u0111\u1ecbnh d\u1ea1ng d\u1eef li\u1ec7u cho c\u00e1c c\u1ed9t l\u00e0 c\u00f3 th\u1ec3 <strong>Close &amp; Load<\/strong> v\u00e0o Excel \u0111\u01b0\u1ee3c r\u1ed3i.<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/17.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30300 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/17-1024x576-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-16\" width=\"1024\" height=\"576\" title=\"17-1024x576-1\"><\/a><\/p>\n<p style=\"text-align: justify\">C\u00f3 tr\u01b0\u1eddng h\u1ee3p\u00a0d\u1eef li\u1ec7u\u00a0n\u00e0o kh\u00f3 \u0111\u1ee1 h\u01a1n n\u1eefa kh\u00f4ng \u0111\u00e2y???. H\u00e3y c\u00f9ng H\u1ecdc Excel Online sang v\u00ed d\u1ee5 3.<\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_3\"><\/span>V\u00ed d\u1ee5 3<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">C\u0169ng v\u1edbi d\u1eef li\u1ec7u gi\u1ed1ng nh\u01b0 c\u00e1c v\u00ed d\u1ee5 tr\u01b0\u1edbc, nh\u01b0ng l\u1ea7n n\u00e0y ti\u00eau \u0111\u1ec1 c\u1ee7a b\u1ea1n l\u1ea1i c\u00f3 Merger (m\u1ed9t \u0111i\u1ec1u m\u00e0 b\u1ea1n n\u00ean h\u1ea1n ch\u1ebf s\u1eed d\u1ee5ng).<\/p>\n<p style=\"text-align: justify\">V\u1edbi tr\u01b0\u1eddng h\u1ee3p ti\u00eau \u0111\u1ec1 c\u1ee7a b\u1ea3ng c\u00f3 Merger th\u00ec ta c\u00f3 m\u1ed9t s\u1ed1 h\u01b0\u1edbng x\u1eed l\u00fd nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>C\u00e1ch 1: X\u1eed l\u00fd ti\u00eau \u0111\u1ec1 tr\u00ean Excel lu\u00f4n<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">V\u1edbi c\u00e1ch n\u00e0y, b\u1ea1n s\u1ebd b\u1ecf Merger\u00a0ngay trong Excel lu\u00f4n\u00a0v\u00e0 \u0111\u1ed5i t\u00ean l\u1ea1i c\u00e1c ti\u00eau \u0111\u1ec1 \u0111\u00f3 ph\u00f9 h\u1ee3p, \u0111\u1ec3 c\u00f3 th\u1ec3 \u0111\u01b0a v\u1ec1 \u0111\u01b0\u1ee3c d\u1ea1ng nh\u01b0 V\u00ed d\u1ee5 2 m\u00e0 H\u1ecdc Excel Online \u0111\u00e3 gi\u1edbi thi\u1ec7u \u1edf tr\u00ean.<\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>C\u00e1ch 2: X\u1eed l\u00fd b\u00ean trong Power Query Editor<\/strong><\/span><\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/18.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30302 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/18-2.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-17\" width=\"702\" height=\"601\" title=\"18-2\"><\/a><\/p>\n<p style=\"text-align: justify\">T\u1ea5t nhi\u00ean l\u00e0 ta c\u0169ng c\u1ea7n ph\u1ea3i chuy\u1ec3n\u00a0d\u1eef li\u1ec7u n\u00e0y v\u1ec1 d\u1ea1ng nh\u01b0 v\u00ed d\u1ee5 2. X\u1eed l\u00fd nh\u01b0 n\u00e0o \u0111\u00e2y trong Power Query???<\/p>\n<p style=\"text-align: justify\">\u0110\u1ea7u ti\u00ean th\u00ec c\u0169ng nh\u01b0 c\u00e1c V\u00ed d\u1ee5 tr\u01b0\u1edbc l\u00e0 b\u1ea1n t\u1ea1o cho n\u00f3 1 Table v\u00e0 \u0111\u1eb7t t\u00ean (m\u00ecnh \u0111\u00e3 \u0111\u1eb7t t\u00ean \u1edf \u0111\u00e2y l\u00e0 VD_3).<\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>L\u01b0u \u00fd:<\/strong><\/span> Tr\u01b0\u1eddng h\u1ee3p n\u00e0y ta s\u1ebd b\u1ecf ch\u1ecdn ph\u1ea7n \u201cMy table has headers\u201d trong h\u1ed9p tho\u1ea1i t\u1ea1o Table.<\/p>\n<p style=\"text-align: justify\">Th\u1ef1c hi\u1ec7n Input d\u1eef li\u1ec7u v\u00e0o Power Query v\u00e0 ta c\u0169ng s\u1ebd c\u1ea7n\u00a0m\u1ed9t s\u1ed1 b\u01b0\u1edbc \u0111\u1ec3 bi\u1ebfn \u0111\u1ed5i d\u1eef li\u1ec7u n\u00e0y v\u1ec1 d\u1ea1ng \u201cChu\u1ea9n\u201d, c\u1ee5 th\u1ec3 nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\">Sau khi Input d\u1eef li\u1ec7u v\u00e0o\u00a0Power Query ta s\u1ebd tr\u00f4ng n\u00f3 nh\u01b0 n\u00e0y \u0111\u00e2y, nh\u1eefng \u00f4 kh\u00f4ng c\u00f3 d\u1eef li\u1ec7u g\u00ec s\u1ebd hi\u1ec7n n<em>ull.<\/em><\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/19.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30303 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/19-1024x356-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-19\" width=\"1024\" height=\"356\" title=\"19-1024x356-1\"><\/a><\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>B\u01b0\u1edbc 1: Th\u1ef1c hi\u1ec7n \u0111\u1ed5i t\u00ean cho c\u00e1c c\u1ed9t (c\u00f3 quy t\u1eafc nh\u1ea5t \u0111\u1ecbnh)<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">\u1ede b\u01b0\u1edbc n\u00e0y, ta s\u1ebd \u0111\u1ed5i t\u00ean cho c\u00e1c c\u1ed9t n\u00e0y theo m\u1ed9t quy t\u1eafc nh\u1ea5t \u0111\u1ecbnh \u0111\u1ec3 c\u00f3 th\u1ec3 v\u1eadn d\u1ee5ng \u0111\u01b0\u1ee3c c\u00e1c b\u01b0\u1edbc trong V\u00ed d\u1ee5 2, c\u1ee5 th\u1ec3 nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>B\u01b0\u1edbc 2: Th\u1ef1c hi\u1ec7n xo\u00e1 d\u00f2ng<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">Ti\u1ebfp theo ta s\u1ebd xo\u00e1 2 d\u00f2ng \u0111\u1ea7u ti\u00ean trong d\u1eef li\u1ec7u b\u1eb1ng ch\u1ee9c n\u0103ng Remove Rows trong Tab Home.<\/p>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2019\/10\/20.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30305 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/20-1024x354-1.png\" alt=\"huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-20\" width=\"1024\" height=\"354\" title=\"20-1024x354-1\"><\/a><\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 14pt\"><strong>B\u01b0\u1edbc 2: Th\u1ef1c hi\u1ec7n c\u00e1c b\u01b0\u1edbc nh\u01b0 v\u00ed d\u1ee5 2<\/strong><\/span><\/p>\n<p style=\"text-align: justify\">Sau khi th\u1ef1c hi\u1ec7n 2 b\u01b0\u1edbc tr\u00ean, d\u1eef li\u1ec7u l\u00fac n\u00e0y s\u1ebd gi\u1ed1ng nh\u01b0 d\u1eef li\u1ec7u g\u1ed1c \u1edf V\u00ed d\u1ee5 2, v\u00e0 b\u1ea1n s\u1ebd th\u1ef1c hi\u1ec7n c\u00e1c b\u01b0\u1edbc t\u01b0\u01a1ng t\u1ef1 \u0111\u1ec3 tr\u1ea3 v\u1ec1 \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u \u0111\u00fang \u201cChu\u1ea9n\u201d. b\u1ea1n <strong>Unpivot -&gt; T\u00e1ch c\u1ed9t -&gt; Pivot<\/strong>, \u0111\u1ec3 ra \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 gi\u1ed1ng k\u1ebft qu\u1ea3 c\u1ee7a V\u00ed d\u1ee5 2.<\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Mot_vai_luu_y\"><\/span>M\u1ed9t v\u00e0i l\u01b0u \u00fd<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">Qua 3 v\u00ed d\u1ee5 x\u1eed l\u00fd d\u1eef li\u1ec7u tr\u00ean, ta c\u00f3 m\u1ed9t v\u00e0i l\u01b0u \u00fd nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\">\u2013 C\u00e1c thao t\u00e1c ta th\u1ef1c hi\u1ec7n trong Power Query Editor s\u1ebd \u0111\u01b0\u1ee3c M Code ghi l\u1ea1i (M Code l\u00e0 ng\u00f4n ng\u1eef m\u00e0 Power Query s\u1eed d\u1ee5ng \u0111\u1ec3 ghi l\u1ea1i c\u00e1c b\u01b0\u1edbc \u0111\u01b0\u1ee3c \u00e1p d\u1ee5ng) g\u1ea7n gi\u1ed1ng nh\u01b0 Code VBA trong Excel. \u0110i\u1ec1u n\u00e0y r\u1ea5t thu\u1eadn khi ch\u00fang ta c\u1eadp nh\u1eadt th\u00eam, b\u1edbt, ch\u1ec9nh s\u1eeda,\u2026. d\u1eef li\u1ec7u \u1edf DATA g\u1ed1c th\u00ec b\u1ea1n ch\u1ec9 c\u1ea7n Refresh. Ngay l\u1eadp t\u1ee9c nh\u1eefng quy tr\u00ecnh b\u1ea1n th\u1ef1c hi\u1ec7n trong Power Query Editor\u00a0s\u1ebd th\u1ef1c thi v\u00e0 c\u1eadp nh\u1eadt k\u1ebft qu\u1ea3 cho b\u1ea1n 1 c\u00e1ch nhanh ch\u00f3ng, ch\u00ednh x\u00e1c.<\/p>\n<p style=\"text-align: justify\">\u2013 Trong b\u00e0i vi\u1ebft n\u00e0y, \u1edf B\u01b0\u1edbc 1 v\u00e0 B\u01b0\u1edbc 2 trong Vi d\u1ee5 3 n\u00e0y\u00a0v\u1eeba r\u1ed3i ta ph\u1ea3i l\u00e0m th\u1ee7 c\u00f4ng, \u0111i\u1ec1u n\u00e0y c\u00f3 th\u1ec3 s\u1ebd c\u00f3 m\u1ed9t s\u1ed1 v\u1ea5n \u0111\u1ec1 nh\u01b0 khi b\u1ea1n th\u00eam\u00a0c\u00e1c tr\u01b0\u1eddng (c\u1ed9t) d\u1eef li\u1ec7u kh\u00e1c v\u00e0o DATA g\u1ed1c th\u00ec vi\u1ec7c \u0111\u1ed5i t\u00ean c\u1ed9t \u0111\u00f3\u00a0Power Query Editor s\u1ebd kh\u00f4ng hi\u1ec3u \u0111\u1ec3 \u0111\u1ed5i cho b\u1ea1n th\u00e0nh t\u00ean g\u00ec. Do \u0111\u00f3, t\u1ed1t h\u01a1n h\u1ebft l\u00e0 b\u1ea1n n\u00ean t\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u 1 c\u00e1ch hi\u1ec7u qu\u1ea3 h\u01a1n.<\/p>\n<p style=\"text-align: justify\">\u2013 T\u1eeb \u0111\u00e2y b\u1ea1n c\u00f3 th\u1ec3 r\u00fat ra: C\u00e1c thao t\u00e1c m\u00e0 ta th\u1ef1c hi\u1ec7n trong\u00a0Power Query Editor c\u1ea7n c\u00f3 t\u00ednh chung, th\u1ed1ng nh\u1ea5t, ta ph\u1ea3i t\u1ea1o ra 1 quy tr\u00ecnh chung trong x\u1eed l\u00fd d\u1eef li\u1ec7u \u0111\u1ec3 m\u1ed7i l\u1ea7n c\u1eadp nh\u1eadt d\u1eef li\u1ec7u g\u1ed1c th\u00ec quy tr\u00ecnh n\u00e0y v\u1eabn ho\u1ea1t \u0111\u1ed9ng \u0111\u00fang v\u00e0 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 ch\u00ednh x\u00e1c cho ch\u00fang ta.<\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Ket_luan\"><\/span>K\u1ebft lu\u1eadn<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\"><strong>H\u1ecdc Excel Online <\/strong>hy v\u1ecdng t\u1edbi \u0111\u00e2y b\u1ea1n \u0111\u00e3 n\u1eafm \u0111\u01b0\u1ee3c c\u00e1ch x\u1eed l\u00fd d\u1eef li\u1ec7u, c<strong>huy\u1ec3n \u0111\u1ed5i d\u1eef li\u1ec7u v\u1ec1 d\u1ea1ng \u201cChu\u1ea9n\u201d v\u1edbi Power Query trong Excel.<\/strong> <a href=\"http:\/\/blog.ezworkapp.com\/power-query-la-gi-tai-sao-can-hoc-power-query.html\"><strong>Power Query<\/strong> <\/a>l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 m\u1ea1nh \u0111\u01b0\u1ee3c Excel h\u1ed7 tr\u1ee3 gi\u00fap cho b\u1ea1n x\u1eed l\u00fd d\u1eef li\u1ec7u, bi\u1ebfn \u0111\u1ed5i d\u1eef li\u1ec7u m\u1ed9t c\u00e1ch kh\u00e1 \u0111\u01a1n gi\u1ea3n, hi\u1ec7u qu\u1ea3 v\u00e0 hi\u1ec7u su\u1ea5t cao. V\u1edbi\u00a0Power Query b\u00e2y gi\u1edd vi\u1ec7c chu\u1ea9n ho\u00e1 d\u1eef li\u1ec7u, g\u1ed9p file, g\u1ed9p c\u00e1c Sheets trong Excel tr\u1edf n\u00ean \u0111\u01a1n gi\u1ea3n h\u01a1n bao gi\u1edd h\u1ebft<\/p>\n<p style=\"text-align: justify\">Power Query l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 r\u1ea5t m\u1ea1nh. Tuy nhi\u00ean,\u00a0c\u00f2n kh\u00e1 m\u1edbi m\u1ebb v\u1edbi ch\u00fang ta. Vi\u1ec7c n\u1eafm \u0111\u01b0\u1ee3c c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng c\u1ee5 n\u00e0y s\u1ebd l\u00e0 c\u1ea7u n\u1ed1i gi\u1eefa Excel t\u1edbi<strong> Power BI \u2013 M\u1ed9t c\u00f4ng c\u1ee5 \u0111\u1ec3 l\u00e0m b\u00e1o c\u00e1o\u00a0tuy\u1ec7t v\u1eddi.<\/strong><\/p>\n<hr>\n<p style=\"padding-left: 30px;text-align: justify\"><em><strong><a href=\"http:\/\/blog.ezworkapp.com\/cach-gop-du-lieu-tu-nhieu-bang-vao-1-bang-su-dung-power-query.html\">C\u00e1ch g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o 1 b\u1ea3ng s\u1eed d\u1ee5ng power query<\/a><\/strong><\/em><\/p>\n<p style=\"padding-left: 30px;text-align: justify\"><em><strong><a href=\"http:\/\/blog.ezworkapp.com\/?s=power+bi\">H\u00e0m DAX trong Power BI<\/a><\/strong><\/em><\/p>\n<p style=\"padding-left: 30px;text-align: justify\"><em><strong><a href=\"https:\/\/hocexcel.online\/course\/preview\/pbi101-microsoft-power-bi-desktop-business-intelligence-trong-tam-tay\">PBI101 \u2013 Microsoft Power BI Desktop, Business Intelligence trong t\u1ea7m tay<\/a><\/strong><\/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-333.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":4172,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-4171","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/4171","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=4171"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/4171\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/4172"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=4171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=4171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=4171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}