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

[quanglinh8990]

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 ạ.

 

[ptm0412]

Thêm 2 step: Add index và Add custom

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

 

[quanglinh8990]

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]

Nếu mình muốn sử dụng hàm DAX trong power pivot thì có được không anh?

 

[quanglinh8990]

Thêm 2 step: Add index và Add custom

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]

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]

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]

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]

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]

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]

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]

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]

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 Var

 

[ongke0711]

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]

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]

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]

Đó 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.

 

[quanglinh8990]

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]

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]

Đã 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