Power Query: Berurusan dengan Beberapa Header Identik - Tips Excel

Daftar Isi

Catatan

Ini adalah salah satu dari rangkaian artikel yang merinci solusi yang dikirim untuk tantangan Podcast 2316.

Dalam masalah pembentukan data asli saya, saya mengalami masalah di awal proses. Data yang masuk akan memiliki banyak kolom dengan judul Q1.

Banyak kolom

Dalam solusi saya, saya membuat rentang bernama "UglyData" dan mengimpornya ke Power Query. Hal ini menyebabkan hasil yang tidak menyenangkan dari Power Query yang mengganti nama kolom saya menjadi Q1_1.

Kolom diganti namanya

Kemudian, setelah tidak berputar, saya harus mengekstrak hanya dua karakter kiri dari header tersebut.

Ada tiga solusi terpisah untuk masalah ini:

  • Wyn Hopkins dan Demosikan Header
  • MF Wong dan hapus centang My Table Has Headers (juga disarankan oleh Peter Bartholomew)
  • Jason M dan hapus saja Promoted Headers (juga disarankan oleh Ondřej Malinský dan Excel MVP John MacDougall)

Inovasi pertama berasal dari Wyn Hopkins di Access Analytic. Alih-alih rentang bernama, Wyn mengonversi data ke tabel menggunakan Ctrl + T. Pada titik ini, kerusakan pada tajuk telah selesai, karena Excel mengubah tajuk menjadi:

Dikonversi ke tabel: Ctrl + T

Setelah Wyn mengambil data ke Power Query, dia kemudian membuka menu drop-down Use First Row as Headers dan memilih Use Headers as First Row. Saya tidak pernah menyadari ini ada di sana. Ini membuat langkah yang disebut Table.DemoteHeaders.

Gunakan tajuk sebagai baris pertama

Tetapi, bahkan dengan peningkatan Wyn, dia masih harus mengekstrak 2 karakter pertama dari header tersebut.

Inovasi kedua adalah teknik MF Wong. Saat dia membuat tabel, dia menghapus centang My Table Has Headers!

Tabel saya memiliki header

Hal ini memastikan bahwa Excel meninggalkan beberapa header Q1 dan tidak perlu mengekstrak sufiks tambahan nanti.

Beberapa header Q1

Saya mengerti ada orang-orang di kamp "Saya suka meja". Video MF Wong mendemonstrasikan bagaimana dia dapat menambahkan karyawan baru di sebelah kanan data dan tabel secara otomatis berkembang. Ada banyak alasan bagus untuk menggunakan tabel.

Tapi, karena saya suka Subtotal, Tampilan Kustom, dan Filter menurut Pilihan, saya cenderung tidak menggunakan Tabel. Jadi, saya menghargai solusi dari Jason M. Dia menyimpan data sebagai rentang bernama UglyData. Segera setelah dia mengimpor data ke Power Query, dia menghapus dua langkah berikut:

Langkah-langkah yang dihapus

Sekarang, dengan data hanya di Baris 1, tidak ada kerumitan memiliki banyak kolom yang disebut Q1.

Banyak kolom Q1

Berikut adalah kode Wyn Hopkin yang menunjukkan DemotedHeaders:

let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"

Kembali ke halaman utama untuk tantangan Podcast 2316.

Baca artikel berikutnya dalam seri ini: Power Query: Hapus ini, Hapus itu, atau hapus apa-apa ?.

Artikel yang menarik...