„Power Query“: kelių identiškų antraščių sprendimas - „Excel“ patarimai

Turinys

Pastaba

Tai vienas iš straipsnių serijos, kuriame pateikiami išsamūs sprendimai, atsiųsti iššūkiui „Podcast 2316“.

Savo pirminėje duomenų formavimo problemoje susidūriau su problema labai anksti. Gaunamuose duomenyse turėjo būti daug stulpelių, kurių antraštė buvo Q1.

Daug kolonų

Savo sprendime sukūriau pavadintą diapazoną „UglyData“ ir importavau jį į „Power Query“. Tai sukėlė nepatenkintą „Power Query“ rezultatą, pervardinus mano stulpelius į Q1_1.

Pervardyti stulpeliai

Vėliau, neišsukęs, turėjau iš tų antraščių išskirti tik kairius du simbolius.

Buvo trys atskiri šios problemos sprendimai:

  • Wynas Hopkinsas ir pažeminimo antraštės
  • MF Wong ir atžymėkite „My Table Has Headers“ (taip pat pasiūlė Peteris Bartholomewas)
  • Jasonas M ir paprasčiausiai ištrinkite reklamuojamas antraštes (taip pat pasiūlė Ondřej Malinský ir „Excel MVP“ John MacDougall)

Pirmoji naujovė buvo Wyno Hopkinso „Access Analytic“. Vietoj pavadinto diapazono Wyn konvertuojo duomenis į lentelę naudodamas „Ctrl + T“. Šiuo metu buvo padaryta žala antraštėms, nes „Excel“ konvertuojo antraštes į:

Konvertuota į lentelę: „Ctrl“ + T

Kai Wynas pervedė duomenis į „Power Query“, jis atidarė išskleidžiamąjį meniu „Naudoti pirmąją eilutę kaip antraštes“ ir kaip „Pirmoji eilutė“ pasirinko Naudoti antraštes. Niekada nesupratau, kad tai yra. Tai sukuria žingsnį, pavadintą „Table.DemoteHeaders“.

Pirmoje eilutėje naudokite antraštes

Bet, net ir patobulėjus Wynui, jis vis tiek vėliau turės iš tų antraščių išskirti pirmuosius 2 simbolius.

Antroji naujovė yra MF Wongo technika. Sukūręs lentelę jis nepažymėjo žymės Mano lentelė turi antraštes!

Mano lentelėje yra antraštės

Tai užtikrina, kad „Excel“ palieka kelias Q1 antraštes ramybėje ir nereikia vėliau ištraukti papildomos galūnės.

Keli Q1 antraštės

Suprantu, kad „Aš myliu stalus“ stovykloje yra žmonių. MF Wongo vaizdo įrašas parodė, kaip jis galėtų pridėti naujų darbuotojų duomenų dešinėje ir lentelė automatiškai išsiplečia. Yra daug svarių priežasčių naudoti lenteles.

Kadangi man patinka tarpinės sumos, pasirinktiniai rodiniai ir filtravimas pagal pasirinkimą, aš paprastai nenaudoju lentelių. Taigi, aš vertinu Jasono M. sprendimą. Jis išsaugojo duomenis kaip nurodytą „UglyData“ diapazoną. Kai tik jis importavo duomenis į „Power Query“, jis ištrynė šiuos du veiksmus:

Ištrinti veiksmai

Dabar, kai duomenys yra tiesiog 1 eilutėje, nėra jokio vargo, kai daug stulpelių vadinami Q1.

Daug Q1 stulpelių

Štai Wyno Hopkino kodas, rodantis „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"

Grįžkite į pagrindinį „Podcast 2316“ iššūkio puslapį.

Perskaitykite kitą šios serijos straipsnį: „Power Query“: ištrinkite tai, ištrinkite tuos ar nieko neištrinkite ?.

Įdomios straipsniai...