Excel MVPs tấn công sự cố xóa dữ liệu trong Power Query - Mẹo Excel

Mục lục

Ghi chú

Đây là một trong loạt bài viết chi tiết các giải pháp được gửi đến cho thử thách Podcast 2316.

Excel MVP Oz Du Soleil từ kênh Excel on Fire trên YouTube đã đề cập đến Người đua bò Brazil Kaique Pachecho. Oz là người đầu tiên nhận thấy rằng tôi đã đi chậm hơn để thêm bốn phần tư.

Video của Oz là:
https://www.youtube.com/watch?v=OluZlF44PNI

Mã của anh ấy là:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

Một giải pháp khác, giải pháp này từ Excel MVP John MacDougall.

John là người đầu tiên nói rằng bằng cách xóa hai bước bổ sung Power Query đã thêm, bạn sẽ loại bỏ các hậu tố lẻ trên các tiêu đề Q1 Q2 Q3 Q4 trùng lặp.
John đã sử dụng cột Chỉ mục sớm sẽ được sử dụng ở cuối để sắp xếp. Nhưng - John đã nối cột chỉ mục của mình sau phần mô tả danh mục. Ông đã sử dụng một ký tự ống đứng | để anh ta có thể phá vỡ dữ liệu sau này.
John đã nhập cột có điều kiện của mình dưới dạng cột Tùy chỉnh thay vì sử dụng giao diện Cột có Điều kiện.

Cột có điều kiện dưới dạng cột tùy chỉnh

Xem video của John tại đây:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Excel MVP Ken Puls, đồng tác giả của cuốn sách M is for (Data) Monkey được gửi đến trong ba giải pháp. Cột điều kiện của anh ta có lẽ là ngắn nhất.

Nhưng giải pháp ưa thích của Ken bỏ qua câu hỏi ban đầu. Thay vì tạo bảng trong Power Query, anh ấy tạo tập dữ liệu có thể xoay trong Power Query và sau đó kết thúc bằng bảng tổng hợp.

Bản xem trước cuối cùng của Ken trong Power Query trông như thế này:

Tập dữ liệu có thể xoay vòng

Đây là mã của Ken:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Sau khi chỉ tạo truy vấn này dưới dạng kết nối, anh ta sử dụng bảng tổng hợp để tạo báo cáo cuối cùng.