Power Query: Darījumu ar vairākām identiskām galvenēm - Excel padomi

Piezīme

Šis ir viens no rakstu sērijas, kurā sīki aprakstīti Podcast 2316 izaicinājumam nosūtītie risinājumi.

Sākotnējā datu veidošanas uzdevumā es ļoti agri saskāros ar problēmu. Ienākošajiem datiem bija daudz kolonnu ar virsrakstu Q1.

Daudz kolonnu

Savā risinājumā es izveidoju nosaukto diapazonu “UglyData” un importēju to Power Query. Tas noveda pie nelaimīgā rezultāta, kad Power Query pārdēvēja manas slejas par Q1_1.

Pārdēvētas kolonnas

Vēlāk, pēc pagriešanas, man bija jāizņem no šīm galvenēm tikai kreisās divas rakstzīmes.

Šai problēmai bija trīs atsevišķi risinājumi:

  • Vins Hopkinss un pazemināšanas galvenes
  • MF Wong un noņemiet atzīmi no izvēles rūtiņas My Table Has Headers (ieteica arī Pīters Bartolomejs)
  • Džeisons M un vienkārši izdzēsiet reklamētās galvenes (ieteica arī Ondžejs Malinskis un Excel MVP Džons Makdugals)

Pirmais jauninājums bija Wyn Hopkins pie Access Analytic. Nosaukta diapazona vietā Vins konvertēja datus tabulā, izmantojot Ctrl + T. Šajā brīdī virsrakstu bojājumi tika izdarīti, jo Excel konvertēja virsrakstus uz:

Konvertēts uz tabulu: Ctrl + T

Kad Vins ieveda datus Power Query, viņš atvēra nolaižamo izvēlni Izmantot pirmo rindu kā galvenes un izvēlējās Izmantot galvenes kā pirmo rindu. Es nekad nesapratu, ka tas tur ir. Tas izveido soli ar nosaukumu Table.DemoteHeaders.

Kā pirmo rindu izmantojiet galvenes

Bet, pat uzlabojot Vinu, viņam tomēr vēlāk būs jāizvelk pirmās 2 rakstzīmes no šīm galvenēm.

Otrais jauninājums ir MF Vonga tehnika. Kad viņš izveidoja tabulu, viņš neatzīmēja izvēles rūtiņu Manam galdam ir galvenes!

Manā tabulā ir galvenes

Tas nodrošina, ka programma Excel atstāj vairākas Q1 galvenes un vēlāk nav nepieciešams izvilkt papildu sufiksu.

Vairākas Q1 galvenes

Es saprotu, ka nometnē “Es mīlu galdus” ir cilvēki. MF Vonga video parādīja, kā viņš var pievienot jaunus darbiniekus pa labi no datiem, un tabula automātiski paplašinās. Tabulu izmantošanai ir daudz labu iemeslu.

Bet, tā kā man patīk Starpsummas, Pielāgoti skati un Filtrēšana pēc atlases, es parasti neizmantoju tabulas. Tātad, es novērtēju Džeisona M. risinājumu. Viņš saglabāja datus kā UglyData nosaukto diapazonu. Tiklīdz viņš importēja datus Power Query, viņš izdzēsa šīs divas darbības:

Dzēstas darbības

Tagad, kad dati ir vienkārši 1. rindā, nav problēmu ar daudzām kolonnām, ko sauc par Q1.

Daudzas Q1 kolonnas

Šeit ir Vina Hopkina kods, kas parāda 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"

Atgriezieties uz Podcast 2316 izaicinājuma galveno lapu.

Izlasiet nākamo šīs sērijas rakstu: Power Query: Dzēst šo, Dzēst tos vai neko neizdzēst ?.

Interesanti raksti...