Bạn có thể có 2 tiêu đề trong excel không?

Người dùng thường định dạng báo cáo Excel có tiêu đề bao gồm hai hoặc nhiều hàng cho tiêu đề, thay vì sử dụng một ô duy nhất có ngắt từ. Tôi cũng đã thấy các tệp văn bản có vấn đề tương tự. Xem xét ví dụ sau

Đưa thông tin này vào Power Query đôi khi có thể là một thách thức. Tôi sẽ chỉ cho bạn hai cách để làm điều đó. Cách đầu tiên sẽ là thực hiện thủ công chủ yếu bằng giao diện người dùng Power Query. Cách thứ hai sẽ là một chức năng tùy chỉnh sẽ thực hiện tất cả công việc cho bạn. Để đơn giản, tôi sẽ sử dụng Power Query trong Excel cho việc này vì dữ liệu của tôi đã có trong Excel, nhưng logic tương tự sẽ giữ nguyên nếu bạn đang nhập dữ liệu vào Power Bi. Chúng tôi không muốn hợp nhất các tiêu đề trong Excel. Bạn sẽ chỉ phải thực hiện lại thủ công vào lần tiếp theo khi bạn nhận được tệp từ ai đó. Hãy nhớ - không bao giờ chuyển đổi trước dữ liệu của bạn trước khi bạn chuyển đổi dữ liệu đó trong Power Query

Phương pháp Thủ công Sử dụng Giao diện Power Query

Nếu bạn đang sử dụng Excel, chỉ cần đặt con trỏ chuột vào dữ liệu ở đâu đó rồi đến dải băng Dữ liệu, chọn “Từ Trang tính” nếu bạn đang sử dụng Office 365. Nếu bạn có phiên bản Excel cũ hơn, nó có thể nói Từ bảng hoặc Phạm vi. Đảm bảo bạn bỏ chọn “Bảng của tôi có tiêu đề” vì nó sẽ chỉ đặt hàng trên cùng làm tiêu đề và chúng tôi không muốn điều đó. Khi bạn bấm OK, dữ liệu của bạn trong cửa sổ Power Query sẽ giống như thế này

bảng gốc

Nếu Các bước đã áp dụng của bạn ở bên phải bảng có bước Hàng đầu tiên được quảng cáo cho Tiêu đề và Loại thay đổi, hãy xóa cả hai. Chúng tôi chỉ muốn mọi thứ nằm trong vùng dữ liệu, với các cột có tên Cột1, Cột2, v.v.

Ví dụ này có hai hàng cần được đặt làm tiêu đề. Tuy nhiên, quy trình bên dưới có thể xử lý bất kỳ số lượng hàng tiêu đề nào. Chỉ cần thay đổi 2 thành bất cứ thứ gì nó cần

Chúng ta cần chia bảng này thành hai phần - hai hàng đầu tiên và mọi thứ trừ hai hàng đầu tiên. Trước tiên, trên dải băng Trang chủ Power Query, hãy chọn nút Keep Rows, Keep Top Rows, sau đó nhập 2

Bây giờ, hãy chuyển đến dải băng Transform và chọn Transpose. Dữ liệu của bạn sẽ trông như thế này

Vì vậy, chúng tôi đã giữ hai hàng trên cùng và xoay nó 90 độ để nó nằm nghiêng. Bây giờ chúng ta chỉ cần kết hợp hai cột đó, hoán vị lại chúng, sau đó chúng ta cần quay lại và lấy dữ liệu thực tế. Đây là cách chúng ta sẽ làm điều đó

Đầu tiên, chọn cả hai cột, sau đó trên dải băng Chuyển vị, chọn Hợp nhất Cột và đặt tên là Tiêu đề Mới. Tôi đã chọn một khoảng trắng làm dấu phân cách

Bây giờ để đảm bảo không có khoảng trắng thừa, nhấp chuột phải vào cột mới và chọn lệnh Transform, Trim, sau đó một lần nữa trên dải băng Transpose, nút Transpose. Bây giờ chúng ta có cái này

Chúng tôi gần như ở đó. Chúng tôi chỉ cần dữ liệu của mình mà chúng tôi đã loại bỏ. Nó vẫn ở đó trong dòng Nguồn của các bước đã áp dụng của chúng tôi. Điều này sẽ yêu cầu sửa đổi một chút mã M trong thanh công thức

Để thực hiện việc này dễ dàng hơn trong vài phút, bạn có thể có một bước gọi là “Transposed Table1” trong các bước đã áp dụng của mình. Đổi tên “NewHeader” không có dấu cách.

Tiếp theo, nhấp chuột phải vào NewHeader và chọn Insert Step After. Trong thanh công thức của bạn, nó sẽ ghi =NewHeader. Thay đổi nó để đọc =Source. Bạn sẽ thấy chúng tôi có toàn bộ bàn của chúng tôi trở lại. Bây giờ, hãy chuyển đến ruy-băng Trang chủ và chọn Xóa Hàng, Xóa Hàng Trên cùng và nhập 2 - cùng số lượng hàng tiêu đề mà bảng Excel ban đầu của chúng tôi có. Bây giờ chúng ta chỉ nên có dữ liệu của mình mà không có tiêu đề nào cả.

Tôi sẽ đổi tên bước này là “DataOnly” (một lần nữa, không có dấu cách) thay vì tên Hàng trên cùng đã bị xóa. Đây là nơi ta cảm thấy thú vị

Chuyển đến Dải băng Trang chủ và chọn Thêm Truy vấn. Chọn cùng một truy vấn bạn đang ở. Chúng tôi sẽ nối truy vấn này với chính nó. Nó sẽ hiển thị (Hiện tại) trong tên bảng

Ngay bây giờ dữ liệu của chúng tôi được nhân đôi lên và chúng tôi không có tiêu đề. Nhìn vào thanh công thức. Nó sẽ hiển thị điều này

=Table.Combine({DataOnly, DataOnly})

Thay đổi tham số DataOnly đầu tiên thành NewHeader. Bây giờ có vẻ hợp lý hơn và chúng tôi có một hàng tiêu đề ở trên cùng

Bây giờ, trên ruy-băng Trang chủ, chọn Sử dụng Hàng đầu tiên làm Tiêu đề và thay đổi kiểu dữ liệu của bạn. đây là kết quả cuối cùng

Tại thời điểm này, có lẽ bạn sẽ bỏ cột Tổng thay đổi, hủy xoay cột Dân số để đưa nó vào một bảng Dữ kiện đẹp mắt cho mô hình dữ liệu của Power BI, nhưng đó là một bài viết khác. Bạn có thể sử dụng phương pháp này cho các tiêu đề của bất kỳ số hàng nào. Tôi đã thấy một số hệ thống ERP xuất dữ liệu với 2, 3 hoặc 4 hàng tiêu đề. Chỉ cần thay đổi số hàng bạn giữ hoặc bỏ qua ở trên để tương ứng với điều đó

Phương thức chức năng tùy chỉnh

Bây giờ bạn đã hiểu logic, đây là một chức năng tùy chỉnh mà bạn có thể sử dụng để thực hiện tất cả công việc đó cho mình. Tôi sẽ không xem qua mã, nhưng nó đã được nhận xét. Tôi đã làm hoàn toàn khác để nó động tùy thuộc vào số lượng hàng tiêu đề, xử lý các loại dữ liệu khác nhau trong tiêu đề cột và số lượng cột trong tập dữ liệu gốc. Xem bên dưới về cách đặt cái này trong mô hình của bạn

(OriginalTable as table, HeaderRows as number, optional Delimiter as text) =>
let
    DelimiterToUse = if Delimiter = null then " " else Delimiter,
    HeaderRowsOnly = Table.FirstN(OriginalTable, HeaderRows),
    /*  Convert the header rows to a list of lists. Each row is a full list
        with the number of items in the list being the original number of columns*/
    ConvertedToRows = Table.ToRows(OriginalTable),
    /* Counter used by List.TransformMany to iterate over the lists (row data) in the list. */
    ListCounter = {0..(HeaderRows - 1)},
    /*  for each list (row of headers) iterate through each one and
        convert everything to text. This can be important for Excel
        data where it is pulled in from an Excel Table and is kept
        as the Any data type. You cannot later combine numerical
        and text data using Text.Combine */
    Transformation = 
        List.TransformMany(
            ListCounter,
            each {ConvertedToRows{_}}, 
            (Counter, EachList) => List.Transform(EachList, Text.From)
        ),
    /*  Convert the list of lists (rows) to a list of lists (each column of headers is now in
        a list - so you'll have however many lists you originally had columns, and each list
        will have the same number of elements as the number of header rows you give it in the
        2nd parameter */
    ZipHeaders = List.Zip(Transformation),
    /*  Combine those lists back to a single value. Now there is just a list of the actual
        column header, combined as one value, using a space, or the chosen delimiter. */
    CombineHeaders = 
        List.Transform(
            ZipHeaders, 
            each Text.Trim(Text.Combine(_, DelimiterToUse))
        ),
    /*  Convert this list back to a single column table. */
    BackToTable = Table.FromList(CombineHeaders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    /*  Transpose this table back to a single row. */
    TransposeToRow = Table.Transpose(BackToTable),
    /*  Append the original data from the source table to this. */
    NewTable = 
        Table.Combine(
            {
                TransposeToRow, 
                Table.Skip(OriginalTable, HeaderRows)
            }
        ),
    /*  Promote the new first row to the header. */
    PromoteHeaders = Table.PromoteHeaders(NewTable, [PromoteAllScalars=true])
in
    PromoteHeaders

Để thêm phần này vào Power Query, hãy làm như sau

1. Tạo một truy vấn trống mới. Trong Power Query của Excel, đó là Nguồn mới, Khác, Truy vấn trống. Trong Power BI, đó là Nguồn mới, Truy vấn trống

2. Nhấp chuột phải vào truy vấn mới đó và chọn Trình chỉnh sửa nâng cao

3. Xóa tất cả mã ở đó để nó trống 100%

4. Sao chép mã ở trên - mọi thứ từ hàng đầu tiên có “(OriginalTable” đến hàng cuối cùng có nội dung “PromoteHeaders” - và dán vào Trình chỉnh sửa nâng cao

5. Nhấn Xong để lưu lại

6. Đổi tên nó. Nó có thể được gọi là thứ gì đó vô dụng như Query1. Tôi thường sử dụng “fn” làm tiền tố cho các chức năng tùy chỉnh và bạn không muốn có dấu cách trong đó. Vì vậy, fnConsolidateHeaders là một ví dụ

Nó sẽ giống như thế này trong cửa sổ Power Query

Bây giờ để sử dụng cái này. Nó rất đơn giản. Bạn muốn bắt đầu tại thời điểm dữ liệu của bạn trong Power Query trông giống như hình trên có tên là “Bảng gốc. ” Đường lên đỉnh. Đúng, điều đó đúng - đó là ngay sau khi đưa lưới dữ liệu Excel của bạn vào Power Query và phải là bước Nguồn hoặc có thể là bước Điều hướng tùy thuộc vào cách dữ liệu của bạn được đưa vào. Xin lưu ý. nếu bạn có bước Loại thay đổi hoặc Tiêu đề được quảng cáo sau hàng Nguồn, tôi khuyên bạn nên xóa chúng

Bây giờ, nhấp chuột phải vào bước cuối cùng đó và chọn Chèn bước sau. Bất cứ thứ gì đang hiển thị trong thanh công thức là tên của bảng bạn cần sửa đổi. Tôi sẽ cho rằng đó là Nguồn cho việc này. Trong thanh công thức, bọc nó bằng chức năng mới này

Đây là cú pháp của hàm

fnConsolidateHeaders(Table as table, Number of Header Rows as number, optional Delimiter as text)

• Bảng - đây là tên bảng - vì vậy Nguồn hoặc bất kỳ tên nào của bước ngay lập tức ở trên là. Một lần nữa, nhấp chuột phải vào bước đó và chọn chèn Bước sau và tên bảng sẽ ở đó trong thanh công thức cho bạn

• Số hàng tiêu đề - nhập số nguyên cho biết có bao nhiêu hàng tiêu đề trong dữ liệu. 2, 3 gì cũng được

• Dấu tách tùy chọn - đây là những gì bạn muốn giữa các hàng tiêu đề khi chúng được nối với nhau. Đây là tùy chọn. Công thức sẽ sử dụng khoảng trắng nếu bạn không đặt gì ở đó, nhưng bạn có thể sử dụng bất kỳ (các) ký tự nào bạn muốn. Bạn thậm chí có thể không có dấu phân cách nếu bạn nhập hai dấu ngoặc kép không có gì ở giữa - ““. Đây là trường văn bản, vì vậy bạn phải đặt dấu phân cách trong dấu ngoặc kép