{"id":1906,"date":"2024-12-02T06:31:55","date_gmt":"2024-12-02T06:31:55","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/power-query-chuc-nang-tuy-chinh\/"},"modified":"2024-12-02T06:31:55","modified_gmt":"2024-12-02T06:31:55","slug":"power-query-chuc-nang-tuy-chinh","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/power-query-chuc-nang-tuy-chinh\/","title":{"rendered":"POWER QUERY \u2013 CH\u1ee8C N\u0102NG T\u00d9Y CH\u1ec8NH"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">Trong b\u00e0i \u0111\u0103ng n\u00e0y,\u00a0 <strong>H\u1ecdc Excel Online<\/strong> s\u1ebd kh\u00e1m ph\u00e1 ch\u1ee7 \u0111\u1ec1 v\u1ec1<strong> c\u00e1c h\u00e0m t\u00f9y ch\u1ec9nh.<\/strong>\u00a0N\u00f3 l\u00e0 ai? Khi n\u00e0o ch\u00fang ta n\u00ean s\u1eed d\u1ee5ng ch\u00fang? V\u00e0 l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 t\u1ea1o ra ch\u00fang? B\u00e0i vi\u1ebft n\u00e0y, c\u00f3 h\u01a1n 600 h\u00e0m ti\u00eau chu\u1ea9n \u0111\u00e3 c\u00f3 trong<a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-dua-du-lieu-ve-dang-chuan-voi-power-query-trong-excel.html\"><strong> Power Query<\/strong><\/a>. H\u00e3y c\u00f9ng <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> t\u00ecm hi\u1ec3u ngay sau \u0111\u00e2y.<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">Tuy nhi\u00ean, nh\u1eefng h\u00e0m \u0111\u00f3 kh\u00f4ng ph\u1ea3i l\u00fac n\u00e0o c\u0169ng th\u1ef1c hi\u1ec7n nh\u1eefng g\u00ec ch\u00fang ta c\u1ea7n ho\u1eb7c theo c\u00e1ch hi\u1ec7u qu\u1ea3 nh\u1ea5t, do \u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 ph\u1ea3i chuy\u1ec3n sang c\u00e1c h\u00e0m t\u00f9y ch\u1ec9nh \u0111\u1ec3 \u0111\u1ea1t \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng mong mu\u1ed1n.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-34036\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-power-query-chuc-nang-tuy-chinh.png\" width=\"960\" height=\"540\" title=\"1-power-query-chuc-nang-tuy-chinh\"><\/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-674d545a10883\" 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-chuc-nang-tuy-chinh.html#Cac_chuc_nang_tuy_chinh_la_gi\" title=\"C\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh l\u00e0 g\u00ec?\">C\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh l\u00e0 g\u00ec?<\/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-chuc-nang-tuy-chinh.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-3\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Chuc_nang_tuy_chinh_de_dang\" title=\"Ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh d\u1ec5 d\u00e0ng\">Ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh d\u1ec5 d\u00e0ng<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Giai_ma_M\" title=\"Gi\u1ea3i m\u00e3 M\">Gi\u1ea3i m\u00e3 M<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Khai_bao_tham_so\" title=\"Khai b\u00e1o tham s\u1ed1\">Khai b\u00e1o tham s\u1ed1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Nhieu_tham_so_va_kieu\" title=\"Nhi\u1ec1u tham s\u1ed1 v\u00e0 ki\u1ec3u\">Nhi\u1ec1u tham s\u1ed1 v\u00e0 ki\u1ec3u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Khai_bao_kieu_dau_vao\" title=\"Khai b\u00e1o ki\u1ec3u \u0111\u1ea7u v\u00e0o\">Khai b\u00e1o ki\u1ec3u \u0111\u1ea7u v\u00e0o<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Cac_chuc_nang_tuy_chinh_co_chua_cac_phep_bien_doi\" title=\"C\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh c\u00f3 ch\u1ee9a c\u00e1c ph\u00e9p bi\u1ebfn \u0111\u1ed5i\">C\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh c\u00f3 ch\u1ee9a c\u00e1c ph\u00e9p bi\u1ebfn \u0111\u1ed5i<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Doi_ten_truy_van\" title=\"\u0110\u1ed5i t\u00ean truy v\u1ea5n\">\u0110\u1ed5i t\u00ean truy v\u1ea5n<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Su_chuyen_doi\" title=\"S\u1eed chuy\u1ec3n \u0111\u1ed5i\">S\u1eed chuy\u1ec3n \u0111\u1ed5i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Kiem_tra\" title=\"Ki\u1ec3m tra\">Ki\u1ec3m tra<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Su_dung_ham\" title=\"S\u1eed d\u1ee5ng h\u00e0m\">S\u1eed d\u1ee5ng h\u00e0m<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Buoc_tiep_theo\" title=\"B\u01b0\u1edbc ti\u1ebfp theo\">B\u01b0\u1edbc ti\u1ebfp theo<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/blog.hocexcel.online\/power-query-chuc-nang-tuy-chinh.html#Ket_luan\" title=\"K\u1ebft lu\u1eadn\">K\u1ebft lu\u1eadn<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Cac_chuc_nang_tuy_chinh_la_gi\"><\/span><b>C\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh l\u00e0 g\u00ec?<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Ch\u1ee9c n\u0103ng l\u00e0 c\u00e1c quy tr\u00ecnh ho\u1eb7c c\u00e1c b\u01b0\u1edbc th\u1ef1c hi\u1ec7n m\u1ed9t c\u00e1ch nh\u1ea5t qu\u00e1n c\u00f4ng vi\u1ec7c. V\u00ed d\u1ee5: <a href=\"http:\/\/blog.ezworkapp.com\/cac-vi-du-ve-ham-sum-trong-excel-cach-chi-tinh-tong-cac-cot-hang-hoac-o-duoc-hien-thi.html\"><strong>H\u00e0m SUM trong Excel<\/strong><\/a> s\u1ebd li\u00ean t\u1ee5c c\u1ed9ng c\u00e1c gi\u00e1 tr\u1ecb ho\u1eb7c<strong> <a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-trong-excel-huong-dan-su-dung-chi-tiet-va-co-vi-du-cu-the.html\">h\u00e0m VLOOKUP<\/a> <\/strong>s\u1ebd lu\u00f4n tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb kh\u1edbp t\u01b0\u01a1ng \u0111\u01b0\u01a1ng t\u1eeb danh s\u00e1ch.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tuy nhi\u00ean, v\u00ec c\u00e1c h\u00e0m trong Excel th\u01b0\u1eddng t\u00ednh to\u00e1n c\u00e1c k\u1ebft qu\u1ea3 d\u1ea1ng s\u1ed1 v\u00e0 v\u0103n b\u1ea3n, \u0111i\u1ec1u n\u00e0y kh\u00f4ng c\u00f3 ngh\u0129a l\u00e0 tr\u01b0\u1eddng h\u1ee3p n\u00e0y x\u1ea3y ra. M\u1ed9t h\u00e0m c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 th\u1ef1c hi\u1ec7n nhi\u1ec1u t\u00e1c v\u1ee5 kh\u00e1c nhau v\u00e0 \u0111i\u1ec1u n\u00e0y \u0111\u1eb7c bi\u1ec7t \u0111\u00fang v\u1edbi Power Query. Ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng m\u1ed9t ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh \u0111\u1ec3 l\u1ea5y d\u1eef li\u1ec7u t\u1eeb m\u1ed9t trang web ho\u1eb7c \u0111\u1ec3 \u0111\u1ecdc d\u1eef li\u1ec7u trong m\u1ed9t t\u1ec7p. T\u1eeb g\u00f3c \u0111\u1ed9 Power Query, t\u1ed1t h\u01a1n l\u00e0 b\u1ea1n n\u00ean xem c\u00e1c h\u00e0m nh\u01b0 b\u1ea5t k\u1ef3 t\u00e1c v\u1ee5 th\u00f4ng th\u01b0\u1eddng c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c \u00e1p d\u1ee5ng l\u1eb7p \u0111i l\u1eb7p l\u1ea1i.<\/span><\/p>\n<p><span style=\"font-weight: 400\">T\u00f4i \u0111\u00e3 suy ngh\u0129 kh\u00e1 l\u00e2u v\u1ec1 vi\u1ec7c c\u00f3 n\u00ean \u0111\u01b0a c\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh v\u00e0o lo\u1ea1t b\u00e0i n\u00e0y kh\u00f4ng v\u00ec ch\u00fang c\u00f3 th\u1ec3 kh\u00e1 ph\u1ee9c t\u1ea1p. Nh\u01b0ng t\u00f4i quy\u1ebft \u0111\u1ecbnh \u0111\u00f3 l\u00e0 gi\u00e1 tr\u1ecb \u0111\u1ec3 lo\u1ea1i tr\u1eeb. B\u00e0i \u0111\u0103ng n\u00e0y s\u1ebd li\u00ean quan \u0111\u1ebfn m\u1ed9t ch\u00fat m\u00e3 h\u00f3a v\u00e0 thay \u0111\u1ed5i m\u00e3 M \u0111\u01b0\u1ee3c t\u1ea1o t\u1ef1 \u0111\u1ed9ng, v\u00ec v\u1eady h\u00e3y theo d\u00f5i c\u1ea9n th\u1eadn.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ch\u00fang ta s\u1ebd gi\u1ea3i quy\u1ebft ch\u1ee9c n\u0103ng n\u00e0y b\u1eb1ng c\u00e1ch l\u00e0m vi\u1ec7c qua hai v\u00ed d\u1ee5 (1) m\u1ed9t h\u00e0m t\u00f9y ch\u1ec9nh d\u1ec5 d\u00e0ng (2) h\u00e0m t\u00f9y ch\u1ec9nh d\u1ef1a tr\u00ean chuy\u1ec3n \u0111\u1ed5i.<\/span><\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-58.gif\" title=\"vba101_92019-nho-58\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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-140.gif\" title=\"ex101_92019-nho-140\"><br \/>\n<\/a>\n<\/div>\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\">Ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng c\u00f9ng m\u1ed9t t\u1ec7p nh\u01b0 m\u1ed9t b\u00e0i tr\u01b0\u1edbc. C\u00e1c v\u00ed d\u1ee5 b\u00ean d\u01b0\u1edbi s\u1eed d\u1ee5ng t\u1ec7p c\u00f3 t\u00ean <\/span><b>Example 15 \u2013 <a href=\"https:\/\/drive.google.com\/file\/d\/1eAwH2YsdzGs2BHzS-5duFsUavP5Kpj2R\/view?usp=sharing\">If Function.xlsx<\/a>.<\/b><\/p>\n<p><span style=\"font-weight: 400\">T\u1ec7p v\u00ed d\u1ee5 ch\u1ec9 ch\u1ee9a m\u1ed9t B\u1ea3ng. \u0110\u1ec3 t\u1ea3i d\u1eef li\u1ec7u v\u00e0o Power Query, h\u00e3y ch\u1ecdn b\u1ea5t k\u1ef3 \u00f4 n\u00e0o trong B\u1ea3ng \u0111\u00f3 v\u00e0 nh\u1ea5p v\u00e0o <\/span><b>Data -&gt; From Table\/Range.<\/b><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1eb7t t\u00ean h\u1eefu \u00edch cho truy v\u1ea5n \u2013 T\u00f4i \u0111\u00e3 g\u1ecdi n\u00f3 l\u00e0 Data v\u00e0 \u0111\u00e2y l\u00e0 c\u00e1ch t\u00f4i s\u1ebd \u0111\u1ec1 c\u1eadp \u0111\u1ebfn truy v\u1ea5n n\u00e0y sau trong b\u00e0i \u0111\u0103ng.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Chuc_nang_tuy_chinh_de_dang\"><\/span><b>Ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh d\u1ec5 d\u00e0ng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Trong v\u00ed d\u1ee5 \u0111\u1ea7u ti\u00ean n\u00e0y, ch\u00fang ta s\u1ebd t\u1ea1o m\u1ed9t h\u00e0m t\u00f9y ch\u1ec9nh r\u1ea5t d\u1ec5 d\u00e0ng. N\u00f3 ho\u00e0n to\u00e0n l\u00e0 \u0111\u1ec3 minh h\u1ecda qu\u00e1 tr\u00ecnh \u0111\u1ec3 khi ch\u00fang ta \u0111i v\u00e0o v\u00ed d\u1ee5 sau, n\u00f3 s\u1ebd kh\u00f4ng ph\u1ea3i l\u00e0 m\u1ed9t r\u1eafc r\u1ed1i \u0111\u1ec3 t\u00ecm hi\u1ec3u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">T\u1ea1o truy v\u1ea5n tr\u1ed1ng b\u1eb1ng c\u00e1ch nh\u1ea5p v\u00e0o <\/span><b>Home -&gt; New Source -&gt; Other Sources -&gt; Blank Query.<\/b><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1eb7t t\u00ean cho truy v\u1ea5n, v\u00ec m\u1ee5c \u0111\u00edch minh h\u1ecda, t\u00f4i \u0111\u00e3 \u0111\u1eb7t l\u00e0 myFunction1 (kh\u00f4ng s\u1eed d\u1ee5ng t\u00ean th\u1eadt, h\u00e3y ch\u1ecdn m\u1ed9t c\u00e1i g\u00ec \u0111\u00f3 m\u00f4 t\u1ea3 h\u01a1n, nh\u01b0ng n\u00f3 s\u1ebd ho\u1ea1t \u0111\u1ed9ng cho minh h\u1ecda c\u1ee7a ch\u00fang ta).<\/span><\/p>\n<p><span style=\"font-weight: 400\">M\u1edf <\/span><span style=\"font-weight: 400\">Advanced Editor <\/span><span style=\"font-weight: 400\">cho truy v\u1ea5n \u0111\u00f3 b\u1eb1ng c\u00e1ch nh\u1ea5p v\u00e0o <\/span><b>View -&gt; Advanced Editor<\/b><span style=\"font-weight: 400\"> (or <\/span><b>Home -&gt; Advanced Editor<\/b><span style=\"font-weight: 400\">).<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 Advanced Editor s\u1ebd gi\u00f4ng nh\u01b0 th\u1ebf n\u00e0y:<\/span><\/p>\n<p><span style=\"font-weight: 400\">M\u1eb7c d\u00f9 \u0111\u00f3 l\u00e0 Truy v\u1ea5n tr\u1ed1ng, ch\u00fang ta v\u1eabn \u0111\u01b0\u1ee3c cung c\u1ea5p m\u1ed9t s\u1ed1 m\u00e3 ban \u0111\u1ea7u. C\u00e2u l\u1ec7nh let v\u00e0 in s\u1ebd v\u1eabn c\u00f2n trong m\u00e3 cu\u1ed1i c\u00f9ng, nh\u01b0ng ch\u00fang ta s\u1ebd thay \u0111\u1ed5i ph\u1ea7n c\u00f2n l\u1ea1i c\u1ee7a n\u00f3.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong v\u00ed d\u1ee5 \u0111\u1ea7u ti\u00ean, m\u1ee5c ti\u00eau c\u1ee7a ch\u00fang ta l\u00e0 t\u1ea1o m\u1ed9t h\u00e0m t\u00f9y ch\u1ec9nh s\u1ebd nh\u00e2n gi\u00e1 tr\u1ecb v\u1edbi 10.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1eadp v\u0103n b\u1ea3n sau v\u00e0o Advanced Editor.<\/span><\/p>\n<p><span style=\"font-weight: 400\">GHI NH\u1eda: M\u00e3 M c\u00f3 ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng, v\u00ec v\u1eady h\u00e3y nh\u1eadp c\u1ea9n th\u1eadn.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ch\u00fang ta s\u1ebd l\u00e0m r\u00f5 m\u00e3 sau m\u1ed9t ch\u00fat trong b\u00e0i \u0111\u0103ng, nh\u01b0ng hi\u1ec7n t\u1ea1i, ch\u1ec9 c\u1ea7n nh\u1ea5p v\u00e0o Done \u0111\u1ec3 \u0111\u00f3ng c\u1eeda s\u1ed5 Advanced Editor.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd ch\u00fang ta s\u1ebd th\u1ea5y nh\u1eefng \u0111i\u1ec1u sau \u0111\u00e2y. \u0110\u00e2y l\u00e0 n\u01a1i ch\u00fang ta c\u00f3 th\u1ec3 ki\u1ec3m tra ch\u1ee9c n\u0103ng m\u00e0 ch\u00fang ta \u0111\u00e3 t\u1ea1o. Nh\u1eadp m\u1ed9t s\u1ed1 v\u00e0o Value box v\u00e0 nh\u1ea5p v\u00e0o Invoke.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu ch\u00fang ta \u0111\u00e3 th\u1ef1c hi\u1ec7n m\u1ecdi th\u1ee9 m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c, n\u00f3 s\u1ebd t\u00ednh to\u00e1n gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c v\u00e0 hi\u1ec3n th\u1ecb l\u1ea1i cho ch\u00fang ta.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1eb1ng c\u00e1ch g\u1ecdi h\u00e0m Power Query s\u1ebd t\u1ea1o ra m\u1ed9t truy v\u1ea5n c\u00f3 t\u00ean l\u00e0 <\/span><b>Invoke Function<\/b><span style=\"font-weight: 400\">, m\u00e0 ch\u00fang ta kh\u00f4ng c\u1ea7n \u0111\u1ebfn h\u00e0m n\u00e0y, v\u00ec v\u1eady n\u00f3 c\u00f3 th\u1ec3 b\u1ecb x\u00f3a. Trong c\u00f9ng m\u1ed9t danh s\u00e1ch truy v\u1ea5n, b\u1ea1n s\u1ebd truy v\u1ea5n c\u00e1i m\u00e0 ch\u00fang ta \u0111\u00e3 t\u1ea1o \u1edf \u0111\u1ea7u (m\u00e0 ch\u00fang t\u00f4i g\u1ecdi l\u00e0 Data) v\u00e0 h\u00e0m ch\u00fang ta \u0111\u00e3 t\u1ea1o (g\u1ecdi l\u00e0 myFunction1).<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ti\u1ebfp theo, m\u1edf <\/span><span style=\"font-weight: 400\">Data query<\/span><span style=\"font-weight: 400\"> m\u00e0 ch\u00fang t\u00f4i \u0111\u00e3 t\u1ea1o l\u00fac \u0111\u1ea7u. T\u1ea1o m\u1ed9t <\/span><span style=\"font-weight: 400\">Custom Column <\/span><span style=\"font-weight: 400\">(<\/span><b>Add Column -&gt; Custom Column<\/b><span style=\"font-weight: 400\">) v\u00e0 s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh m\u00e0 ch\u00fang ta \u0111\u00e3 t\u1ea1o.<\/span><\/p>\n<p><span style=\"font-weight: 400\">M\u00e3 \u0111\u1ec3 s\u1eed d\u1ee5ng trong c\u1eeda s\u1ed5 ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh l\u00e0:<\/span><\/p>\n<p><strong>=myFunction1([Value])<\/strong><\/p>\n<p><span style=\"font-weight: 400\">L\u01b0u \u00fd: T\u1eeb [Value} trong h\u00e0m l\u00e0 t\u00ean c\u1ee7a c\u1ed9t \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng l\u00e0m tham s\u1ed1.<\/span><\/p>\n<p><span style=\"font-weight: 400\">S\u1eed d\u1ee5ng m\u1ed9t h\u00e0m t\u00f9y ch\u1ec9nh c\u0169ng gi\u1ed1ng nh\u01b0 s\u1eed d\u1ee5ng m\u1ed9t h\u00e0m Power Query ti\u00eau chu\u1ea9n.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Giai_ma_M\"><\/span><b>Gi\u1ea3i m\u00e3 M<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">H\u00e3y gi\u1ea3i m\u00e3 m\u00e0 ch\u00fang ta \u0111\u00e3 t\u1ea1o tr\u01b0\u1edbc \u0111\u00f3 \u0111\u1ec3 hi\u1ec3u n\u00f3 ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Khai_bao_tham_so\"><\/span><b>Khai b\u00e1o tham s\u1ed1<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">D\u00f2ng \u0111\u1ea7u ti\u00ean khai b\u00e1o c\u00e1c tham s\u1ed1 (ho\u1eb7c \u0111\u1ea7u v\u00e0o) s\u1ebd \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong H\u00e0m. Trong v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, n\u00f3 ch\u1ec9 c\u00f3 m\u1ed9t \u0111\u1ea7u v\u00e0o, m\u00e0 t\u00f4i g\u1ecdi l\u00e0 Value.<\/span><\/p>\n<p><span style=\"font-weight: 400\">D\u00f2ng th\u1ee9 hai x\u00e1c \u0111\u1ecbnh \u0111i\u1ec3m b\u1eaft \u0111\u1ea7u c\u1ee7a c\u00e1c b\u01b0\u1edbc chuy\u1ec3n \u0111\u1ed5i; kh\u00f4ng c\u1ea7n ph\u1ea3i thay \u0111\u1ed5i b\u1ea5t c\u1ee9 \u0111i\u1ec1u g\u00ec trong d\u00f2ng n\u00e0y.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ti\u1ebfp theo, ch\u00fang ta t\u1ea1o m\u1ed9t b\u01b0\u1edbc, \u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 nameOfStep. C\u00f3 m\u1ed9t s\u1ed1 quy t\u1eafc v\u1ec1 c\u00e1c b\u01b0\u1edbc \u0111\u1eb7t t\u00ean, \u0111\u1eb7c bi\u1ec7t l\u00e0 v\u1ec1 vi\u1ec7c s\u1eed d\u1ee5ng d\u1ea5u c\u00e1ch. Tuy nhi\u00ean, thay v\u00ec l\u00e0m \u0111i\u1ec1u \u0111\u00f3 ngay b\u00e2y gi\u1edd, vi\u1ec7c lu\u00f4n lo\u1ea1i tr\u1eeb kho\u1ea3ng tr\u1eafng khi t\u1ea1o c\u00e1c b\u01b0\u1edbc theo c\u00e1ch th\u1ee7 c\u00f4ng s\u1ebd d\u1ec5 d\u00e0ng h\u01a1n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">nameOfStep s\u1ebd l\u1ea5y Value (tham s\u1ed1 \u0111\u01b0\u1ee3c t\u1ea1o \u1edf d\u00f2ng \u0111\u1ea7u ti\u00ean) v\u00e0 nh\u00e2n n\u00f3 v\u1edbi 10.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Cu\u1ed1i c\u00f9ng, ch\u00fang ta tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m. <\/span><b>in<\/b><span style=\"font-weight: 400\"> ph\u1ea7n cu\u1ed1i c\u1ee7a c\u00e1c ph\u00e9p chuy\u1ec3n \u0111\u1ed5i. Sau c\u00e2u l\u1ec7nh <\/span><b>in<\/b><span style=\"font-weight: 400\">, ch\u00fang ta khai b\u00e1o t\u00ean c\u1ee7a b\u01b0\u1edbc s\u1ebd \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1. Trong tr\u01b0\u1eddng h\u1ee3p c\u1ee7a ch\u00fang ta, ch\u00fang ta ch\u1ec9 c\u00f3 m\u1ed9t b\u01b0\u1edbc, v\u00ec v\u1eady ch\u00fang ta s\u1ebd tr\u1ea3 v\u1ec1 nameOfStep.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Nhieu_tham_so_va_kieu\"><\/span><b>Nhi\u1ec1u tham s\u1ed1 v\u00e0 ki\u1ec3u<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Khi s\u1eed d\u1ee5ng c\u00e1c h\u00e0m Power Query ti\u00eau chu\u1ea9n, b\u1ea1n s\u1ebd nh\u1eadn th\u1ea5y ch\u00fang c\u00f3 th\u1ec3 c\u00f3 nhi\u1ec1u tham s\u1ed1 v\u00e0 y\u00eau c\u1ea7u c\u00e1c ki\u1ec3u d\u1eef li\u1ec7u c\u1ee5 th\u1ec3. Ch\u00fang ta c\u00f3 th\u1ec3 l\u00e0m \u0111i\u1ec1u t\u01b0\u01a1ng t\u1ef1 trong c\u00e1c h\u00e0m t\u00f9y ch\u1ec9nh c\u1ee7a ri\u00eang m\u00ecnh, t\u1ea5t c\u1ea3 \u0111\u1ec1u \u0111\u01b0\u1ee3c x\u1eed l\u00fd trong khai b\u00e1o tham s\u1ed1 \u1edf \u0111\u1ea7u truy v\u1ea5n:<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Khai_bao_kieu_dau_vao\"><\/span><b>Khai b\u00e1o ki\u1ec3u \u0111\u1ea7u v\u00e0o<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 khai b\u00e1o m\u1ed9t ki\u1ec3u d\u1eef li\u1ec7u v\u0103n b\u1ea3n, ch\u00fang ta thay \u0111\u1ed5i \u0111o\u1ea1n m\u00e3 nh\u01b0 sau:<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 ch\u1ec9 cho ph\u00e9p m\u1ed9t b\u1ea3ng l\u00e0m tham s\u1ed1, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Kh\u00e1 d\u1ec5 d\u00e0ng, ph\u1ea3i kh\u00f4ng. Nh\u01b0 b\u1ea1n \u0111\u00e3 bi\u1ebft, c\u00f3 r\u1ea5t nhi\u1ec1u ki\u1ec3u d\u1eef li\u1ec7u. N\u1ebfu b\u1ea1n kh\u00f4ng ch\u1eafc ch\u1eafn v\u1ec1 m\u00e3 cho ki\u1ec3u d\u1eef li\u1ec7u, h\u00e3y s\u1eed d\u1ee5ng truy v\u1ea5n kh\u00e1c, th\u1ef1c hi\u1ec7n thay \u0111\u1ed5i ki\u1ec3u d\u1eef li\u1ec7u th\u00f4ng qua giao di\u1ec7n ng\u01b0\u1eddi d\u00f9ng b\u00ecnh th\u01b0\u1eddng, sau \u0111\u00f3 xem l\u1ea1i m\u00e3 \u0111\u00e3 t\u1ea1o \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh thay \u0111\u1ed5i.<\/span><\/p>\n<p><b>Khai b\u00e1o ki\u1ec3u d\u1eef li\u1ec7u \u0111\u1ea7u ra<\/b><\/p>\n<p><span style=\"font-weight: 400\">Power Query c\u0169ng c\u00f3 ki\u1ec3u d\u1eef li\u1ec7u cho \u0111\u1ea7u ra c\u1ee7a h\u00e0m; \u0111i\u1ec1u n\u00e0y \u0111\u01b0\u1ee3c n\u00eau b\u00ean ngo\u00e0i d\u1ea5u ngo\u1eb7c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5 tr\u00ean cho th\u1ea5y h\u00e0m nh\u1eadn m\u1ed9t gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n l\u00e0m tham s\u1ed1 v\u00e0 k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 m\u1ed9t ki\u1ec3u d\u1eef li\u1ec7u b\u1ea3ng.<\/span><\/p>\n<p><b>Khai b\u00e1o nhi\u1ec1u tham s\u1ed1<\/b><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 khai b\u00e1o nhi\u1ec1u tham s\u1ed1 cho h\u00e0m, ch\u00fang ta li\u1ec7t k\u00ea ch\u00fang b\u00ean trong d\u1ea5u ngo\u1eb7c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Khai b\u00e1o tr\u00ean c\u00f3:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Hai tham s\u1ed1 \u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 (1) myText (v\u1edbi ki\u1ec3u d\u1eef li\u1ec7u v\u0103n b\u1ea3n) v\u00e0 (2) myTable (v\u1edbi ki\u1ec3u d\u1eef li\u1ec7u b\u1ea3ng).<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u0110\u1ea7u ra \u0111\u00e3 \u0111\u01b0\u1ee3c khai b\u00e1o l\u00e0 m\u1ed9t ki\u1ec3u d\u1eef li\u1ec7u s\u1ed1.<\/span><\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Cac_chuc_nang_tuy_chinh_co_chua_cac_phep_bien_doi\"><\/span><b>C\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh c\u00f3 ch\u1ee9a c\u00e1c ph\u00e9p bi\u1ebfn \u0111\u1ed5i<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Khi ch\u00fang ta <\/span><span style=\"font-weight: 400\">nh\u1eadp d\u1eef li\u1ec7u t\u1eeb web<\/span><span style=\"font-weight: 400\"> trong m\u1ed9t b\u00e0i \u0111\u0103ng tr\u01b0\u1edbc, ch\u00fang ta \u0111\u00e3 s\u1eed d\u1ee5ng c\u00f9ng m\u1ed9t ph\u01b0\u01a1ng ph\u00e1p. H\u00e3y xem b\u00e0i vi\u1ebft \u0111\u00f3 \u0111\u1ec3 th\u1ef1c h\u00e0nh th\u00eam m\u1ed9t ch\u00fat. Tuy nhi\u00ean, l\u1ea7n n\u00e0y, ch\u00fang ta s\u1ebd l\u00e0m m\u1ed9t th\u1ee9 g\u00ec \u0111\u00f3 ph\u1ee9c t\u1ea1p h\u01a1n m\u1ed9t ch\u00fat.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ed1i v\u1edbi v\u00ed d\u1ee5 n\u00e0y, ch\u00fang ta s\u1ebd t\u00ednh to\u00e1n % giao d\u1ecbch tr\u00ean t\u1ed5ng s\u1ed1 l\u1ea7n mua h\u00e0ng cho kh\u00e1ch h\u00e0ng \u0111\u00f3. V\u00ec v\u1eady, n\u1ebfu ABC gi\u1ea3m gi\u00e1 \u0111\u00e3 mua $122 th\u00ec ph\u1ea7n tr\u0103m c\u1ee7a t\u1ed5ng s\u1ed1 \u0111\u00f3 l\u00e0 bao nhi\u00eau ph\u1ea7n tr\u0103m cho m\u1ed7i giao d\u1ecbch. H\u00ecnh d\u01b0\u1edbi \u0111\u00e2y l\u00e0 k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng m\u00e0 ch\u00fang ta \u0111ang c\u1ed1 g\u1eafng t\u00ednh to\u00e1n. ABC c\u00f3 hai giao d\u1ecbch, giao d\u1ecbch \u0111\u1ea7u ti\u00ean l\u00e0 37,7% v\u00e0 th\u1ee9 hai l\u00e0 62,3%.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong thu\u1eadt ng\u1eef Excel, s\u1ef1 chuy\u1ec3n \u0111\u1ed5i n\u00e0y gi\u1ed1ng nh\u01b0 chia gi\u00e1 tr\u1ecb trong m\u1ed7i h\u00e0ng cho SUMIFS c\u1ee7a Customer.<\/span><\/p>\n<p><b>Nh\u00e2n b\u1ea3n truy v\u1ea5n<\/b><\/p>\n<p><span style=\"font-weight: 400\">Sao y Truy v\u1ea5n Data m\u00e0 ch\u00fang ta \u0111\u00e3 nh\u1eadp l\u00fac \u0111\u1ea7u. \u0110i\u1ec1u n\u00e0y l\u00e0 do ch\u00fang ta c\u1ea7n (1) m\u1ed9t truy v\u1ea5n d\u01b0\u1edbi d\u1ea1ng m\u1ed9t h\u00e0m (2) m\u1ed9t truy v\u1ea5n \u0111\u1ec3 s\u1eed d\u1ee5ng h\u00e0m. \u0110\u1ec3 th\u1ef1c hi\u1ec7n vi\u1ec7c n\u00e0y, h\u00e3y nh\u1ea5p chu\u1ed9t ph\u1ea3i v\u00e0o <\/span><span style=\"font-weight: 400\">query<\/span><span style=\"font-weight: 400\"> trong danh s\u00e1ch truy v\u1ea5n v\u00e0 nh\u1ea5p v\u00e0o <\/span><b>Duplicate<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Doi_ten_truy_van\"><\/span><b>\u0110\u1ed5i t\u00ean truy v\u1ea5n<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Nh\u01b0 ch\u00fang ta \u0111\u00e3 th\u1ea5y \u1edf tr\u00ean, t\u00ean m\u00e0 ch\u00fang ta \u0111\u1eb7t cho truy v\u1ea5n \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 g\u1ecdi h\u00e0m.<\/span><\/p>\n<p><span style=\"font-weight: 400\">T\u00f4i \u0111\u00e3 \u0111\u1eb7t t\u00ean cho truy v\u1ea5n l\u00e0 fnCustomerTotal. \u0110\u1eb7t fn \u1edf \u0111\u1ea7u \u0111\u00e3 tr\u1edf th\u00e0nh th\u00f4ng l\u1ec7 ph\u1ed5 bi\u1ebfn cho c\u00e1c ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh; n\u00f3 gi\u00fap x\u00e1c \u0111\u1ecbnh m\u1ed9t c\u00e1ch tr\u1ef1c quan r\u1eb1ng n\u00f3 l\u00e0 m\u1ed9t ch\u1ee9c n\u0103ng.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Su_chuyen_doi\"><\/span><b>S\u1eed chuy\u1ec3n \u0111\u1ed5i<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">X\u00f3a t\u1ea5t c\u1ea3 c\u00e1c b\u01b0\u1edbc, ngo\u1ea1i tr\u1eeb b\u01b0\u1edbc Source.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ti\u1ebfp theo, nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Group By<\/b><span style=\"font-weight: 400\">. Ch\u00fang ta \u0111\u00e3 bi\u1ebft c\u00e1ch s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng Group By trong ph\u1ea7n tr\u01b0\u1edbc c\u1ee7a lo\u1ea1t b\u00e0i n\u00e0y. Trong c\u1eeda s\u1ed5 Group By, nh\u1eadp c\u00e1c c\u00e0i \u0111\u1eb7t sau:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Column: Customer<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">New column name: Customer Total<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Operation: Sum<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Column: Value<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o OK.<\/span><\/p>\n<p><span style=\"font-weight: 400\">L\u1ecdc tr\u00ean c\u1ed9t Customer \u0111\u1ec3 ch\u1ecdn m\u1ed9t kh\u00e1ch h\u00e0ng (Gi\u1ea3m gi\u00e1 ABC trong v\u00ed d\u1ee5 n\u00e0y).<\/span><\/p>\n<p><b>Thay \u0111\u1ed5i truy v\u1ea5n th\u00e0nh m\u1ed9t h\u00e0m<\/b><\/p>\n<p><span style=\"font-weight: 400\">M\u1edf tr\u00ecnh ch\u1ec9nh s\u1eeda n\u00e2ng cao:<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 ph\u1ea7n kh\u00f3 kh\u0103n\u2026 th\u1ef1c hi\u1ec7n c\u00e1c thay \u0111\u1ed5i ch\u00ednh x\u00e1c \u0111\u1ed1i v\u1edbi m\u00e3 M.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Khai b\u00e1o c\u00e1c tham s\u1ed1 l\u00e0 d\u00f2ng \u0111\u1ea7u ti\u00ean trong m\u00e3. Ch\u00fang ta ch\u1ec9 c\u1ea7n hai tham s\u1ed1 (1) b\u1ea3ng \u0111\u1ec3 s\u1eed d\u1ee5ng v\u00e0 (2) t\u00ean kh\u00e1ch h\u00e0ng \u0111\u1ec3 l\u1ecdc.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng m\u1ed9t b\u1ea3ng \u0111\u01b0\u1ee3c khai b\u00e1o l\u00e0m ngu\u1ed3n, ch\u00fang ta kh\u00f4ng c\u1ea7n b\u01b0\u1edbc Source \u0111\u00e3 t\u1ed3n t\u1ea1i trong m\u00e3. X\u00f3a d\u00f2ng sau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong d\u00f2ng ti\u1ebfp theo, v\u1eabn c\u00f3 tham chi\u1ebfu \u0111\u1ebfn b\u01b0\u1edbc Source m\u00e0 ch\u00fang ta v\u1eeba x\u00f3a. Ch\u00fang ta c\u1ea7n tr\u1ecf d\u00f2ng \u0111\u00f3 \u0111\u1ebfn tham s\u1ed1 \u0111\u00e3 khai b\u00e1o.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Thay \u0111\u1ed5i:<\/span><\/p>\n<p><strong><i>#\u201dGrouped Rows\u201d = Table.Group(Source, {\u201cCustomer\u201d}, {{\u201cCustomer Total\u201d, each List.Sum([Value]), type number}}),<\/i><\/strong><\/p>\n<p><strong>Tr\u1edf th\u00e0nh:<\/strong><\/p>\n<p><strong><i>#\u201dGrouped Rows\u201d = Table.Group(sourceTable, {\u201cCustomer\u201d}, {{\u201cCustomer Total\u201d, each List.Sum([Value]), type number}}),<\/i><\/strong><\/p>\n<p><strong>B\u00e2y gi\u1edd, h\u00e3y thay \u0111\u1ed5i t\u00ean \u0111\u01b0\u1ee3c thay th\u1ebf Customer th\u00e0nh \u0111\u1ed1i s\u1ed1 customerName \u0111\u00e3 khai b\u00e1o: Thay \u0111\u1ed5i \u0111i\u1ec1u n\u00e0y:<\/strong><\/p>\n<p><strong><i>#\u201dFiltered Rows\u201d = Table.SelectRows(#\u201dGrouped Rows\u201d, each ([Customer] = \u201cABC Discounts\u201d))<\/i><\/strong><\/p>\n<p><strong>V\u00e0o n\u00e0y:<\/strong><\/p>\n<p><strong><i>#\u201dFiltered Rows\u201d = Table.SelectRows(#\u201dGrouped Rows\u201d, each ([Customer] = customerName))<\/i><\/strong><\/p>\n<p><span style=\"font-weight: 400\">Ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00e1i n\u00e0y \u1edf d\u1ea1ng hi\u1ec7n t\u1ea1i c\u1ee7a n\u00f3. N\u1ebfu ch\u00fang ta mu\u1ed1n, n\u00f3 s\u1ebd tr\u1ea3 v\u1ec1 m\u1ed9t B\u1ea3ng ch\u1ee9a m\u1ed9t gi\u00e1 tr\u1ecb duy nh\u1ea5t, thay v\u00ec m\u1ed9t gi\u00e1 tr\u1ecb ri\u00eang l\u1ebb. Nh\u01b0ng ch\u00fang ta c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n th\u00eam b\u01b0\u1edbc \u0111\u1ec3 ch\u1ec9 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Th\u1ef1c hi\u1ec7n c\u00e1c thay \u0111\u1ed5i sau \u0111\u1ed1i v\u1edbi m\u00e3 M. Thay \u0111\u00f4\u0309i:<\/span><\/p>\n<p><strong><i>#\u201dFiltered Rows\u201d = Table.SelectRows(#\u201dGrouped Rows\u201d, each ([Customer] = customerName))<\/i><\/strong><\/p>\n<p><strong><i>in<\/i><\/strong><\/p>\n<p><strong><i>\u00a0\u00a0\u00a0\u00a0#\u201dRemoved Columns\u201d<\/i><\/strong><\/p>\n<p><strong>V\u00e0o n\u00e0y:<\/strong><\/p>\n<p><strong><i>#\u201dFiltered Rows\u201d = Table.SelectRows(#\u201dGrouped Rows\u201d, each ([Customer] = customerName)),<\/i><\/strong><\/p>\n<p><strong><i>\u00a0\u00a0\u00a0\u00a0CustomerTotal = Record.Field(#\u201dFiltered Rows\u201d{0},\u201dCustomer Total\u201d)<\/i><\/strong><\/p>\n<p><strong><i>in<\/i><\/strong><\/p>\n<p><strong><i>\u00a0\u00a0\u00a0\u00a0CustomerTotal<\/i><\/strong><\/p>\n<p><span style=\"font-weight: 400\">Record.Field l\u00e0 m\u1ed9t h\u00e0m Power Query tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb \u0111\u01a1n l\u1ebb t\u1eeb m\u1ed9t c\u1ed9t:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><b>#\u201dFiltered Rows\u201d{0}<\/b><span style=\"font-weight: 400\"> \u0111\u1ea1i di\u1ec7n cho h\u00e0ng \u0111\u1ea7u ti\u00ean t\u1eeb b\u1ea3ng v\u00ec n\u00f3 t\u1ed3n t\u1ea1i sau b\u01b0\u1edbc H\u00e0ng \u0111\u01b0\u1ee3c L\u1ecdc<\/span><\/li>\n<li style=\"font-weight: 400\"><b>\u201cCustomer Total\u201d<\/b> <span style=\"font-weight: 400\">l\u00e0 t\u00ean c\u1ee7a c\u1ed9t<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">C\u1ea3nh b\u00e1o: b\u1ea1n c\u00f3 nh\u1eadn th\u1ea5y d\u1ea5u ph\u1ea9y b\u1ed5 sung \u1edf cu\u1ed1i d\u00f2ng<\/span><b>#\u201dRemoved Columns\u201d<\/b><span style=\"font-weight: 400\"> kh\u00f4ng?\u00a0 N\u00f3 c\u1ea7n ph\u1ea3i \u1edf \u0111\u00f3, v\u00ec v\u1eady h\u00e3y th\u00eam n\u00f3 v\u00e0o, n\u1ebfu b\u1ea1n \u0111\u00e3 b\u1ecf l\u1ee1 n\u00f3.<\/span><\/p>\n<p><span style=\"font-weight: 400\">M\u00e3 M ho\u00e0n ch\u1ec9nh s\u1ebd nh\u01b0 th\u1ebf n\u00e0y:<\/span><\/p>\n<p><strong><i>(sourceTable as table, customerName as text) =&gt;<\/i><\/strong><\/p>\n<p><strong><i>let<\/i><\/strong><\/p>\n<p><strong><i>\u00a0\u00a0\u00a0\u00a0#\u201dGrouped Rows\u201d = Table.Group(sourceTable, {\u201cCustomer\u201d}, {{\u201cCustomer Total\u201d, each List.Sum([Value]), type number}}),<\/i><\/strong><\/p>\n<p><strong><i>\u00a0\u00a0\u00a0\u00a0#\u201dFiltered Rows\u201d = Table.SelectRows(#\u201dGrouped Rows\u201d, each ([Customer] = customerName)),<\/i><\/strong><\/p>\n<p><strong><i>\u00a0\u00a0\u00a0\u00a0CustomerTotal = Record.Field(#\u201dFiltered Rows\u201d{0},\u201dCustomer Total\u201d)<\/i><\/strong><\/p>\n<p><strong><i>in<\/i><\/strong><\/p>\n<p><strong><i>\u00a0\u00a0\u00a0\u00a0CustomerTotal<\/i><\/strong><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o Done \u0111\u1ec3 \u0111\u00f3ng c\u1eeda s\u1ed5 <\/span><span style=\"font-weight: 400\">Advanced Editor.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Kiem_tra\"><\/span><b>Ki\u1ec3m tra<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">H\u00e3y ki\u1ec3m tra h\u00e0m. Ch\u1ecdn b\u1ea3ng <\/span><span style=\"font-weight: 400\">Data query<\/span><span style=\"font-weight: 400\"> \u0111\u1ec3 s\u1eed d\u1ee5ng l\u00e0m <\/span><span style=\"font-weight: 400\">sourceTable <\/span><span style=\"font-weight: 400\">v\u00e0 nh\u1eadp <\/span><span style=\"font-weight: 400\">Customer Name<\/span><span style=\"font-weight: 400\">. Nh\u1ea5p v\u00e0o <\/span><b>Invoke.<\/b><\/p>\n<p><span style=\"font-weight: 400\">N\u00f3 s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb<\/span><\/p>\n<p><span style=\"font-weight: 400\">Th\u1eadt b\u1ea5t ng\u1edd<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Su_dung_ham\"><\/span><b>S\u1eed d\u1ee5ng h\u00e0m<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">T\u1ea5t c\u1ea3 nh\u1eefng g\u00ec c\u00f2n l\u1ea1i b\u00e2y gi\u1edd l\u00e0 ch\u00fang ta s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh. M\u1edf truy v\u1ea5n d\u1eef li\u1ec7u v\u00e0 th\u00eam m\u1ed9t <\/span><span style=\"font-weight: 400\">Custom Column<\/span><span style=\"font-weight: 400\">. Nh\u1eadp th\u00f4ng tin sau v\u00e0o c\u00f4ng th\u1ee9c:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Source l\u00e0 t\u00ean c\u1ee7a b\u01b0\u1edbc (t\u1ee9c l\u00e0 s\u1eed d\u1ee5ng b\u1ea3ng nh\u01b0 \u0111\u00e3 t\u1ed3n t\u1ea1i sau khi b\u01b0\u1edbc Sourcre \u0111\u00e3 ho\u00e0n th\u00e0nh)<\/span><\/li>\n<li style=\"font-weight: 400\"><b>[Customer] <\/b><span style=\"font-weight: 400\">l\u00e0 c\u1ed9t kh\u00e1ch h\u00e0ng<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">B\u1ea5m OK \u0111\u1ec3 ch\u1ea5p nh\u1eadn ch\u1ee9c n\u0103ng<\/span><\/p>\n<p><span style=\"font-weight: 400\">Cu\u1ed1i c\u00f9ng, th\u1ef1c hi\u1ec7n c\u00e1c chuy\u1ec3n \u0111\u1ed5i sau:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Thay \u0111\u1ed5i c\u1ed9t m\u1edbi th\u00e0nh ki\u1ec3u d\u1eef li\u1ec7u %<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u1eafp x\u1ebfp theo c\u1ed9t Customer<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">X\u00f3a c\u1ed9t Sold By<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 Xem tr\u01b0\u1edbc b\u00e2y gi\u1edd s\u1ebd gi\u1ed1ng nh\u01b0 th\u1ebf n\u00e0y, hi\u1ec3n th\u1ecb % ph\u00e2n t\u00edch cho m\u1ed7i giao d\u1ecbch c\u1ee7a kh\u00e1ch h\u00e0ng.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_tiep_theo\"><\/span><b>B\u01b0\u1edbc ti\u1ebfp theo<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n mu\u1ed1n \u0111\u01b0a \u0111i\u1ec1u n\u00e0y l\u00ean c\u1ea5p \u0111\u1ed9 ti\u1ebfp theo, t\u1ea1i sao kh\u00f4ng th\u1eed thay \u0111\u1ed5i ch\u1ee9c n\u0103ng \u0111\u1ec3 n\u00f3 ho\u1ea1t \u0111\u1ed9ng tr\u00ean b\u1ea5t k\u1ef3 c\u1ed9t n\u00e0o, thay v\u00ec ch\u1ec9 m\u1ed9t c\u1ed9t c\u00f3 t\u00ean \u201cCustomer\u201d.\u00a0<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ket_luan\"><\/span><b>K\u1ebft lu\u1eadn<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Trong b\u00e0i \u0111\u0103ng n\u00e0y, ch\u00fang ta \u0111\u00e3 bi\u1ebft c\u00e1ch t\u1ea1o c\u00e1c h\u00e0m t\u00f9y ch\u1ec9nh c\u01a1 b\u1ea3n v\u00e0 d\u1ef1a tr\u00ean chuy\u1ec3n \u0111\u1ed5i\u00a0 Ph\u01b0\u01a1ng ph\u00e1p n\u00e0y c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng b\u1ea5t c\u1ee9 l\u00fac n\u00e0o ch\u00fang ta c\u1ea7n m\u1ed9t gi\u00e1 tr\u1ecb ho\u1eb7c c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng n\u1eb1m trong c\u00f9ng m\u1ed9t h\u00e0ng. M\u1ed9t s\u1ed1 v\u00ed d\u1ee5:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">T\u1ed5ng s\u1ed1 \u0111ang ch\u1ea1y<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">% t\u1ed5ng th\u1ec3<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Gi\u00e1 tr\u1ecb \u1edf h\u00e0ng tr\u01b0\u1edbc ho\u1eb7c h\u00e0ng sau<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Thao t\u00e1c v\u1edbi m\u00e3 M r\u1ea5t kh\u00f3 v\u00e0 d\u1ec5 x\u1ea3y ra sai s\u00f3t, \u0111\u1eb7c bi\u1ec7t l\u00e0 khi ch\u00fang ta ch\u01b0a t\u00edch l\u0169y kinh nghi\u1ec7m. Do \u0111\u00f3, gi\u1ed1ng nh\u01b0 r\u1ea5t nhi\u1ec1u t\u00ednh n\u0103ng kh\u00e1c c\u1ee7a Power Query, l\u1eddi khuy\u00ean t\u1ed1t nh\u1ea5t l\u00e0 h\u00e3y th\u1ef1c h\u00e0nh.<\/span><\/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\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-58.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":1907,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1906","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\/1906","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=1906"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1906\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/1907"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=1906"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=1906"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=1906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}