Huomautus
Tämä on yksi artikkelisarjasta, joka sisältää yksityiskohtaiset ratkaisut, jotka lähetettiin Podcast 2316 -haasteeseen.
Yksi ratkaisuni ongelmista on, että luokkien lopullinen järjestys ei välttämättä vastannut alkuperäistä sarakkeiden järjestystä. Tajusin tämän videoni loppupuolella, ja koska se ei ollut erityisen tärkeä, en murehtinut sitä.
Josh Johnson lähetti kuitenkin ratkaisun, joka käsitteli sen. Kun Josh sanoi käyttäneensä Index-saraketta, oletin, että se oli kuin Power Query: Index and Modulo: Numeroryhmät ennätyksinä 1-5 toistuvasti. Mutta Joshin käyttö oli täysin erilaista.
Huomautus: Excel MVP John MacDougall käytti myös tätä menetelmää, mutta hän yhdisti hakemistosarakkeen luokan kuvauksen loppuun. Katso Johnin video täältä: https://www.youtube.com/watch?v=Dqmb6SEJDXI ja lue lisää hänen koodistaan täältä: Excel MVPs Attack the Data Cleansing Problem in Power Query.
Varhaisessa vaiheessa, kun Joshilla oli vielä vain kuusi tietuetta, hän lisäsi indeksin, joka alkaa numerosta 1. Josh napsautti kaavapalkkia ja nimitti hakemistosarakkeen uudelleen luokaksi.
Luokka -sarake oli uusi viimeinen sarake. Hän käytti Siirrä alkuun -toimintoa siirtääkseen sen ensimmäiseksi:
Tämän jälkeen tapahtuu paljon muita vaiheita. Ne ovat vaiheita, jotka ovat innovatiivisia, mutta joita on käsitelty enimmäkseen muissa artikkeleissa toistaiseksi. Monien tällaisten vaiheiden jälkeen aloin ajatella, että luokan numerot 1-6 olivat vain virhe. Ajattelin, että mahdollisesti Josh aikoo poistaa ne käyttämättä niitä.
Josh Unpivots, sitten ehdollinen sarake, täytä sitten, sitten kääntyy, lisää kokonaissumman. Hän ei koskaan näytä käyttävän kyseistä saraketta. Monien vaiheiden jälkeen hän on täällä:
Mutta sitten viimeisissä vaiheissa Josh lajittelee tiedot työntekijän nimen ja sitten kategorian mukaan!
Tässä vaiheessa hän voi poistaa Luokka-sarakkeen. Viimeinen ero: PTO tulee ennen projektia A, aivan kuten se oli alkuperäisissä sarakkeissa. Se on mukava kosketus.
Huomautan myös, että Josh lähetti videon, jossa hän kävi läpi nämä vaiheet. Kiitos Joshille pikanäppäinten käytöstä Power Queryssä!
Tässä on Joshin koodi:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Palaa Podcast 2316 -haasteen pääsivulle.
Lue tämän sarjan seuraava artikkeli: Excel MVP: t hyökkäävät tiedonpuhdistusongelmaan Power Queryssä.
