Xin các cao thủ giúp đỡ làm lại bảng dữ liệu bằng power query

[TomBui]

Em có một bảng tính gồm rất nhiều loại hàng, em muốn dùng power query để lọc ra những loại hàng đang làm và tính điểm chỉ tiêu. Các các giúp em với. Cám ơn mọi người trước!
Dưới đây là mẫu bảng em muốn thông qua power query để làm ra:

 

[kbw1]

Thì bạn ấy tính toán luôn bằng Power query, cũng sao đâu, power query vẫn tính toán bình thường mà. Tuy nhiên theo ý kiến của tôi thì Power Query nó mạnh về ETL, còn tính toán thì nên là công việc của Power Pivot(Cái này trực quan và tùy biến nó dễ hơn)

Thì vậy nên mình mới thấy lạ,
sao không dùng cách dễ hơn,
mà lại chọn kiểu trình bày trong query.
Lạ quá mà không hiểu,

Bài đã được tự động gộp: 23/6/22

Bạn thử xem. Tôi thấy rằng làm như vậy không tính được tỷ trọng và cột tính điểm

Đoạn Groupby em làm nó không ra,
Không hiểu đoạn đấy mục đích để làm gì ạ.

Bài đã được tự động gộp: 23/6/22

Tôi làm đến đây và bạn làm tiếp:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Độ dày (um)", type text}, {"Kích thước", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước", "Loại"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Mặt hàng", each [#"Một/Hai kính"] & "/" & [Loại pin] & "/" & [#"Độ dày (um)"] & "/" &[Kích thước]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Tổn hao", "Mặt hàng", "Loại", "Attribute", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Tổn hao", "Mặt hàng", "Loại"}, {{"Value", each List.Sum([Value]), type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Loại] <> null and [Loại] <> "Tổn hao")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Loại]), "Loại", "Value", List.Sum)
in
    #"Pivoted Column"

View attachment 277774

Đoạn này em ko chuyển được nó lên thành từng cột,
không rõ thao tác em sai ở đâu ạ.

 

[ptm0412]

Mình cũng đi theo hướng này,
Nhưng không hiểu sao bạn kia lại để dạng Pivot trong query
sau đó lại làm công thức ở query luôn,
Chưa hiểu mục đích chính là gì.
))))))

Mục đích chính là ra kết quả
Chủ đề là Power query, bạn ấy làm bằng query, thắc mắc việc tính trong query không giống excel, nên tôi làm cho bạn ấy bằng query.
File tôi làm đính kèm dưới đây, trong đó có query + Power pivot của hocexcel1991 và query thuần của tôi. Khi refresh thì của tôi lại nhanh hơn do chỉ có 21 dòng, của hocexcel 183 dòng.
Tôi không nói làm thuần power query là hay, tùy trường hợp sẽ phải dùng power pivot (tạo measure bằng DAX).
Ghi chú là Cột Value sau khi unpivot của hocexcel khi đổi type thành Int64 nên mất các giá trị của tổn hao, tất cả tổn hao bằng 0, tôi đã sửa lại type number.

Nếu so sánh với kết quả của tác giả ở bài 17 thì có khác, do bạn ấy lọc chỉ lấy tháng 3, và chỉ lấy các mặt hàng đưa vào <> 0

 

[ptm0412]

Lên chức "tay bó bột" rồi hở

Chưa đến mức bó bột. Hai tháng nay bệnh nền gây biến chứng tê và cứng khớp mất mấy ngón. Mí lị coi trong file thấy tự làm được 1 mớ, chỉ bí chỗ nào thì chỉ chỗ đó.

 

[kbw1]

Mục đích chính là ra kết quả
Chủ đề là Power query, bạn ấy làm bằng query, thắc mắc việc tính trong query không giống excel, nên tôi làm cho bạn ấy bằng query.
File tôi làm đính kèm dưới đây, trong đó có query + Power pivot của hocexcel1991 và query thuần của tôi. Khi refresh thì của tôi lại nhanh hơn do chỉ có 21 dòng, của hocexcel 183 dòng.
Tôi không nói làm thuần power query là hay, tùy trường hợp sẽ phải dùng power pivot (tạo measure bằng DAX).
Ghi chú là Cột Value sau khi unpivot của hocexcel khi đổi type thành Int64 nên mất các giá trị của tổn hao, tất cả tổn hao bằng 0, tôi đã sửa lại type number.

Nếu so sánh với kết quả của tác giả ở bài 17 thì có khác, do bạn ấy lọc chỉ lấy tháng 3, và chỉ lấy các mặt hàng đưa vào <> 0

Em hiểu rồi ạ,
Em chọn hướng đi sang Pivot vì quen thuộc hơn và không đủ khả năng làm trên Query,
Em cảm ơn ạ!

 

[kbw1]

Bạn thử xem. Tôi thấy rằng làm như vậy không tính được tỷ trọng và cột tính điểm

Bác ơi,
Bác giải thích giúp em chỗ này được không ạ,
Em làm đến bước này toàn ra list rồi tính tổng, nó mất luôn bảng không làm tiếp chỗ addTytrong được ạ.

 

[ptm0412]

Bác ơi,
Bác giải thích giúp em chỗ này được không ạ,
Em làm đến bước này toàn ra list rồi tính tổng, nó mất luôn bảng không làm tiếp chỗ addTytrong được ạ.

Đó là 1 bước trung gian tính tổng của cột "Số lượng đưa vào". Kết quả của bước chỉ là 1 con số tổng, dùng số này để tính toán tạo cột tỷ trọng cho bước tiếp theo. Trong lập trình gọi là biến "tổng" và tính gía trị tổng cho biến.
Bước tiếp theo sử dụng giá trị này:

AddTyTrong = Table.AddColumn(RemovedColumns1,"TyTrong", each [Số lượng đưa vào]/ SumSL)

Bước AddTytrong có thể viết gộp thế này:

AddTyTrong = Table.AddColumn(RemovedColumns1,"TyTrong",
each [Số lượng đưa vào]/List.Sum(RemovedColumns1[Số lượng đưa vào]))

Tuy nhiên tính trước thì chỉ tính 1 lần, tránh được việc tính nhiều lần khi gộp lệnh (mỗi dòng phải mỗi tính tổng)

 

[kbw1]

Đó là 1 bước trung gian tính tổng của cột "Số lượng đưa vào". Kết quả của bước chỉ là 1 con số tổng, dùng số này để tính toán tạo cột tỷ trọng cho bước tiếp theo. Trong lập trình gọi là biến "tổng" và tính gía trị tổng cho biến.

Em chưa hiểu bước này bác thao tác như thế nào mà ra được kiểu đó ạ,
Em thao tác nó bị ra list tính toán sum, nên không làm bước tiếp theo được ạ,
Bước sau thì em xem bác add thêm column em hiểu rồi ạ,

Bác cho em hỏi thêm 1 chút, em thấy hình như bác vs em làm cứ khác khác,
Em chỉ biết thao tác bấm chọn trên query thôi ạ,
Hình như bác dùng công thức hay làm kiểu gì mà em nghe thấy lạ lắm ạ.

 

[ptm0412]

Hình như bác dùng công thức hay làm kiểu gì mà em nghe thấy lạ lắm ạ.

Tôi viết trực tiếp trong Advanced editor. Chỉ cần thuộc hàm là viết được.

 

[TomBui]

Chào bác @ptm0412 , em còn một bảng nữa trong quá trình làm có một cột cần bác hỗ trợ:

Em muốn fill down nhưng hàng 3L lại nằm ở cột "Loại", cột "Hàng" không có giá trị dẫn đến khi fill down nội dung sẽ hiển thị là 3U (do bảng dữ liệu khá dài nên không thể copy tay sang cột hàng được). Bác có cách nào xử lý vấn đề này không?

Chú thích: Hình ảnh là kết quả mong muốn sau khi điều chỉnh

 

[ptm0412]

Chào bác @ptm0412 , em còn một bảng nữa trong quá trình làm có một cột cần bác hỗ trợ:

Dùng 1 cột phụ HangTmp = if [Hàng] = null and [Loại] <> null then [Loại] else null
Sau đó filldown cột phụ này

 

[truongvu317]

if [Hàng] = null

Bác cho cháu hỏi là thay bằng [Hàng] Is null thì có sự khác biệt gì không bác?

 

[ptm0412]

Bác cho cháu hỏi là thay bằng [Hàng] Is null thì có sự khác biệt gì không bác?

Thử biết ngay í mà

 

[TomBui]

Dùng 1 cột phụ HangTmp = if [Hàng] = null and [Loại] <> null then [Loại] else null
Sau đó filldown cột phụ này

Em làm xong ra được như vậy:

Còn các loại hàng khác ghép với cột phụ mới như thế nào để ra 1 cột tổng các loại hàng vậy bác

 

[ptm0412]

Em làm xong ra được như vậy:

Còn các loại hàng khác ghép với cột phụ mới như thế nào để ra 1 cột tổng các loại hàng vậy bác

Tôi viết hơi sai, mà bạn cũng làm sai so với cái sai đó.
Nếu làm đúng theo cái sai thì



Viết đúng là if [Hàng] = null and [Loại] <> null then [Loại] else [Hàng]



Sau đó fill down cột tạm

 

[kbw1]

Tôi viết trực tiếp trong Advanced editor. Chỉ cần thuộc hàm là viết được.

View attachment 277838

Em chỉ biết thao tác trên query thôi ạ,
Em không biết viết hàm như trên.

Bài đã được tự động gộp: 2/7/22

Em làm xong ra được như vậy:
View attachment 278161
Còn các loại hàng khác ghép với cột phụ mới như thế nào để ra 1 cột tổng các loại hàng vậy bác
View attachment 278162

Mình làm theo gợi ý của bác trên ra được đây.

 

[truongvu317]

Thử biết ngay í mà

Phải thêm hàm not vào nó mới ưng cái bụng bác ạ. hihi