Xin giúp đỡ: Tạo cột mới trong power query để ra giá trị theo mong muốn

Blue Softs Liên hệ QC
Status
Không mở trả lời sau này.

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Em đang có 01 bảng dữ liệu, muốn tạo 1 cột mới trong power query đặt là Transfer với mô tả như sau:

- Trong tuần 15: Dept thay đổi từ Finance --> HR. Kết quả cột Transfer: ngày 13/4: -1, ngày 14/3: +1
- Trong tuần 16: Dept thay đổi từ Finance --> HR --> QA. Kết quả cột Transfer: ngày 18/4: -1, ngày 19/4: +1, ngày 21/4: -1, ngày 22/4: +1
Chi tiết như hình bên dưới.

Em có đính kèm file. Mong các Anh/Chị/Cao nhân giúp dùm em. Cảm ơn rất nhiều ạ.
HC - Test.PNG
 

File đính kèm

  • HC - Test.xlsx
    124.1 KB · Đọc: 31

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
11,169
Được thích
31,974
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Thêm 2 step: Add index và Add custom
PHP:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddedCustom = Table.AddColumn(AddedIndex, "Transfer", each try if [Index]=0 then null
    else if [Dept] <> AddedIndex[Dept]{[Index]-1} then -1
    else if [Dept] <> AddedIndex[Dept]{[Index]+1} then 1
    else null otherwise null, Int64.Type)
in
    AddedCustom
 
Lần chỉnh sửa cuối:

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Dạ, em rất cảm ơn anh. Em đã ra được kết quả rồi với cách làm ở trên rồi.
 

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Nếu mình muốn sử dụng hàm DAX trong power pivot thì có được không anh?
 

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Thêm 2 step: Add index và Add custom
PHP:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddedCustom = Table.AddColumn(AddedIndex, "Transfer", each try if [Index]=0 then null
    else if [Dept] <> AddedIndex[Dept]{[Index]-1} then -1
    else if [Dept] <> AddedIndex[Dept]{[Index]+1} then 1
    else null otherwise null, Int64.Type)
in
    AddedCustom
Data lớn quá nên em add column trong power query chạy rất là lâu. Nên có thể xử lý trong power vivot được không ạ?
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
11,169
Được thích
31,974
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Data lớn quá nên em add column trong power query chạy rất là lâu. Nên có thể xử lý trong power vivot được không ạ?
Tôi hơi yếu về DAX nên không thử được, nhưng dù gì cũng là tạo cột mới với điều kiện khá đặc biệt, nên nếu dùng DAX (có thể nhanh hơn, tôi không chắc), nhưng vẫn phải chậm
 

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Tôi hơi yếu về DAX nên không thử được, nhưng dù gì cũng là tạo cột mới với điều kiện khá đặc biệt, nên nếu dùng DAX (có thể nhanh hơn, tôi không chắc), nhưng vẫn phải chậm
Cảm ơn Anh. Em đã làm được bằng cách, tạo 2 cột Previousday và Nextday sau đó looupvalue rồi đi so sánh 2 giá trị đó với giá trị ban đầu, theo cách so sánh bên power query của Anh.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
11,169
Được thích
31,974
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Cảm ơn Anh. Em đã làm được bằng cách, tạo 2 cột Previousday và Nextday sau đó looupvalue rồi đi so sánh 2 giá trị đó với giá trị ban đầu, theo cách so sánh bên power query của Anh.
Nếu có ngày trùng sẽ sai
Nếu ngày tháng chưa sắp xếp cũng sai
 

VetMini

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
12,028
Được thích
15,326
Tôi hơi yếu về DAX nên không thử được, nhưng dù gì cũng là tạo cột mới với điều kiện khá đặc biệt, nên nếu dùng DAX (có thể nhanh hơn, tôi không chắc), nhưng vẫn phải chậm
Tôi thì ghét làm "cao nhơn" như thớt đòi hỏi cho nên tôi chỉ trả lời cho bạn:
Sử dụng power Query khi các bảng có thể nối (join) nhau dễ dàng , data model gần như chuẩn hóa , các cột tính toán chỉ tính toán trên dữ liệu cùng dòng .
Sử dụng DAX khi công thức của cột tính toán cần dữ liệu từ dòng khác, bảng khác, ...

Nếu bị chậm do thì do data model không chuẩn hoặc sử dụng không đúng chứ khả năng do số dữ liệu nhiều là rất thấp.
Tôi thì thấy mấy người làm việc với dữ liệu khủng đáng lẽ phải là bậc thầy của tôi, nhưng không hiểu sao cứ lên đây hỏi các cách làm việc căn bản. Có lẽ tại vậy mà họ đặc cách đòi hỏi "cao nhơn" giải quyết chăng ?
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
11,169
Được thích
31,974
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Tôi thì ghét làm "cao nhơn" như thớt đòi hỏi cho nên tôi chỉ trả lời cho bạn:
Sử dụng power Query khi các bảng có thể nối (join) nhau dễ dàng , data model gần như chuẩn hóa , các cột tính toán chỉ tính toán trên dữ liệu cùng dòng .
Sử dụng DAX khi công thức của cột tính toán cần dữ liệu từ dòng khác, bảng khác, ...
Power query tôi dùng ở bài #2 là so sánh từng dòng (cùng cột) với 2 dòng liền kề trên và dưới. Có thể khi dữ liệu nhiều (ví dụ 10 ngàn dòng), sẽ phải so sánh 20 ngàn lần nên chậm chăng? Vì tôi chưa nghĩ sâu nên chưa biết có cách nào cải thiện.
 

VetMini

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
12,028
Được thích
15,326
Power query tôi dùng ở bài #2 là so sánh từng dòng (cùng cột) với 2 dòng liền kề trên và dưới. Có thể khi dữ liệu nhiều (ví dụ 10 ngàn dòng), sẽ phải so sánh 20 ngàn lần nên chậm chăng? Vì tôi chưa nghĩ sâu nên chưa biết có cách nào cải thiện.
Người sử dụng dữ liệu khủng thì đồng thời cũng sẽ tự biết thử những cách khác nhau. Cái tử "Power" nó cũng ngầm chứa ý "power to try everything at all possible angles"
Bởi vậy tôi mới nói họ là bậc thầy của tôi.
Tôi không nói thêm gì nữa vì sẽ có những người bị chạm tự ái và cãi tùm lum lên.
 

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Dữ liệu của em là data nhân viên từ năm 2000 đến nay, khoảng 20.000 rows và cứ 1 nhân viên sẽ có 365 rows, tương ứng với dữ liệu chấm công cho 1 năm. Data em đang xử lý là từ 2020 đến nay nên khá là lớn. Vì, em mới tập tành chạy power query và pivot nên em nghĩ, khi add 1 column mới với công thức so sánh như vậy trong power query thì sẽ xử lý rất lâu và thực tế là 3 giờ chạy vẫn chưa xong.
 

excel_lv1.5

Thành viên tích cực
Tham gia ngày
20 Tháng mười 2017
Bài viết
868
Được thích
1,558
Giới tính
Nam
Cảm ơn Anh. Em đã làm được bằng cách, tạo 2 cột Previousday và Nextday sau đó looupvalue rồi đi so sánh 2 giá trị đó với giá trị ban đầu, theo cách so sánh bên power query của Anh.
Dax bạn có thể viết vầy thêm cột Index trong transform power query, khi đã sử dụng data model thì nên hạn chế thêm cột (tiêu chuẩn 10 cột, dòng không giới hạn bởi vậy mới có unpivot), tính toán thì Dax nhanh hơn power query là chắc rồi, Power query chỉ mạnh khi ETL thôi, có thể sử dụng EARLIER để bỏ các biến khai báo Var1623690651162.png
 

ongke0711

Thành viên tích cực
Tham gia ngày
7 Tháng chín 2006
Bài viết
941
Được thích
1,110
Giới tính
Nam
Dữ liệu của em là data nhân viên từ năm 2000 đến nay, khoảng 20.000 rows và cứ 1 nhân viên sẽ có 365 rows, tương ứng với dữ liệu chấm công cho 1 năm. Data em đang xử lý là từ 2020 đến nay nên khá là lớn. Vì, em mới tập tành chạy power query và pivot nên em nghĩ, khi add 1 column mới với công thức so sánh như vậy trong power query thì sẽ xử lý rất lâu và thực tế là 3 giờ chạy vẫn chưa xong.

Dữ liệu thời gian dài như vậy sao bạn không cắt ra mà xử lý, tội gì mà phải tải dữ liệu xa lơ xa lắc lên cho mệt máy.
Cắt dữ liệu trong khoảng thời gian cần xử lý ra bảng tạm (tất nhiên cũng phải cố định câu trúc tên field) rồi chạy các công cụ của bạn.
Cách xử lý của tôi đối với dữ liệu chấm công này là: sau khi chốt công tính lương, lưu dữ liệu kết quả xuống Table. Dữ liệu chấm công (365 ngày của từng người) sẽ xoá sau 3 - 6 tháng hoặc chuyển vào CSDL lưu, tách khỏi nguồn dữ liệu của ứng dụng.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
11,169
Được thích
31,974
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Tôi không biết mục đích cuối cùng của cột transfer là gì, nhưng quan sát thấy nó là đánh dấu ngày bắt đầu và ngày kết thúc của 1 Dept của 1 nhân viên.
Trong phần mềm chắc chắc sẽ có chứng từ điều động luân chuyển nhân viên, chỉ khi có thay đổi mới phát sinh chứng từ, nên dữ liệu sẽ rất ít; tại sao không lấy thông tin từ bảng data Employee_Transfer?
Bảng dữ liệu đang xử lý chắc chắn là bảng phái sinh từ những chứng từ trên, nên hãy lấy từ gốc, chứ 1 năm đã là 7.3 triệu dòng công thức nào chịu nổi
 

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Dữ liệu thời gian dài như vậy sao bạn không cắt ra mà xử lý, tội gì mà phải tải dữ liệu xa lơ xa lắc lên cho mệt máy.
Cắt dữ liệu trong khoảng thời gian cần xử lý ra bảng tạm (tất nhiên cũng phải cố định câu trúc tên field) rồi chạy các công cụ của bạn.
Cách xử lý của tôi đối với dữ liệu chấm công này là: sau khi chốt công tính lương, lưu dữ liệu kết quả xuống Table. Dữ liệu chấm công (365 ngày của từng người) sẽ xoá sau 3 - 6 tháng hoặc chuyển vào CSDL lưu, tách khỏi nguồn dữ liệu của ứng dụng.
Đó là bạn làm công việc monthly, còn cái mình đang làm là dashboard qua các năm nên dữ liệu phải có đủ để run.
 

ongke0711

Thành viên tích cực
Tham gia ngày
7 Tháng chín 2006
Bài viết
941
Được thích
1,110
Giới tính
Nam
Đó là bạn làm công việc monthly, còn cái mình đang làm là dashboard qua các năm nên dữ liệu phải có đủ để run.

Dashboard có chi tiết đến đơn vị ngày luôn à? Không phải theo tháng, năm? Chi tiết dữ vậy.
Tôi chỉ cần thông kê tới đơn vị tháng, quý, năm thôi. Với dữ liệu này thì mỗi nhân viên chỉ có 12 dòng ngày công tháng/năm.
Bạn nên nhìn tổng thể, bài trên là tôi gợi ý việc tách dữ liệu ra mà xử lý, lưu dữ liệu sau xử lý xuống table theo từng mốc thời gian, khi cần chỉ móc dữ liệu đã được xử lý này ra mà làm chứ không nhất thiết phải chạy lại toàn bộ dữ liệu. SQL Server là hệ quản trị CSDL lớn mà còn có hỗ trợ thêm một phần #Temp Table để người ta xử lý dữ liệu nữa mà.
Thôi ngưng nhiều chuyện tại đây. :)
 
Lần chỉnh sửa cuối:

quanglinh8990

Thành viên bị đình chỉ hoạt động
Thành viên bị đình chỉ hoạt động
Tham gia ngày
26 Tháng ba 2018
Bài viết
15
Được thích
0
Giới tính
Nam
Dashboard có chi tiết đến đơn vị ngày luôn à? Không phải theo tháng, năm? Chi tiết dữ vậy.
Tôi chỉ cần thông kê tới đơn vị tháng, quý, năm thôi. Với dữ liệu này thì mỗi nhân viên chỉ có 12 dòng ngày công tháng/năm.
Bạn nên nhìn tổng thể, bài trên là tôi gợi ý việc tách dữ liệu ra mà xử lý, lưu dữ liệu sau xử lý xuống table theo từng mốc thời gian, khi cần chỉ móc dữ liệu đã được xử lý này ra mà làm chứ không nhất thiết phải chạy lại toàn bộ dữ liệu. SQL Server là hệ quản trị CSDL lớn mà còn có hỗ trợ thêm một phần #Temp Table để người ta xử lý dữ liệu nữa mà.
Thôi ngưng nhiều chuyện tại đây. :)
Mỗi công ty có 1 model khác nhau. Hiện tại mình là thống kê đến weekly lận bạn. à, đó là data tổng, chứ khi run thì chỉ xử lý từng năm thôi.
 

VetMini

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
12,028
Được thích
15,326
Dashboard có chi tiết đến đơn vị ngày luôn à? Không phải theo tháng, năm? Chi tiết dữ vậy.
...
Thôi ngưng nhiều chuyện tại đây. :)

Đã nói rõ rồi. Nếu phải làm với dữ liệu kiểu đó, dashboards chi tiết cỡ đó (*1) thì là bậc thầy của tôi.
Nếu không phải bậc thầy của tôi thì là bị công ty lợi dụng, giao công việc cấp chuyên viên cho mình.

(*1) đừng nói cách lập dashboard, chỉ riêng kỹ thuật đọc cái dashboard chi tiết mà không rối mắt thì cũng là bậc siêu rồi. Tôi thì mỗi lần đọc cái dashboard của bơ-lum-bớt là phải chuẩn bị uống ly cà phê thật đậm, và chuẩn bị sẵn một ly nữa trên bàn làm việc. Mà máy tôi làm việc thì có hai màn hình để tra cứu dòm qua dòm lại.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
11,169
Được thích
31,974
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Đã nói rõ rồi. Nếu phải làm với dữ liệu kiểu đó, dashboards chi tiết cỡ đó (*1) thì là bậc thầy của tôi.
Bài 15 tôi có gợi ý lấy dữ liệu từ table gốc (chứng từ ghi nhận Employee_Transfer) mà hình như tác giả không quan tâm. Dashboard gì không cần biết, cách lấy dữ liệu từ đâu, lấy bao nhiêu, .... mới quan trọng
 
Status
Không mở trả lời sau này.
Top Bottom