Nota
Questo è uno di una serie di articoli che descrivono in dettaglio le soluzioni inviate per la sfida Podcast 2316.
Nella mia soluzione per rimodellare i dati, volevo un modo per vedere se una colonna conteneva il nome di un dipendente o un valore come Q1, Q2, Q3, Q4. Nella mia soluzione, pensavo che nessuno avrebbe avuto un nome con 2 caratteri, quindi ho aggiunto una colonna per calcolare la lunghezza del testo nella colonna.
Jason M ha evitato la necessità della colonna Lunghezza aggiungendo tre clausole Else If alla sua Colonna condizionale.
Il calcolo condizionale per Employee cerca quindi che Quarter sia Null: if (Quarter) = null then (Category Description) else null.
Ecco il codice M di Jason:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "Quarter", each if (Category Description) = "Q1" then (Category Description) else if (Category Description) = "Q2" then (Category Description) else if (Category Description) = "Q3" then (Category Description) else if (Category Description) = "Q4" then (Category Description) else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Employee", each if (Quarter) = null then (Category Description) else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column1",("Quarter")), #"Filled Down" = Table.FillDown(#"Filled Up",("Employee")), #"Inserted Distinct Count" = Table.AddColumn(#"Filled Down", "Distinct Count", each List.NonNullCount(List.Distinct(((Category Description), (Employee)))), Int64.Type), #"Filtered Rows1" = Table.SelectRows(#"Inserted Distinct Count", each (Distinct Count) 1), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Distinct Count")), #"Removed Columns" = Table.RemoveColumns(#"Removed Columns1",("Category Description")), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ((Employee) "Dept. Total")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", ("Quarter", "Employee"), "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",(("Attribute", "Category Description"))), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"(Quarter)), "Quarter", "Value"), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Reordered Columns" = Table.ReorderColumns(#"Inserted Sum",("Category Description", "Employee", "Q1", "Q2", "Q3", "Q4", "Total")) in #"Reordered Columns"Ondřej Malinský ha inviato una soluzione che utilizzava anche più clausole Else If:
Matthew Wykle ha inviato una soluzione con un altro modo per identificare i quarti. Il suo metodo controlla sia che il testo inizi con Q sia che la seconda cifra sia inferiore a 5:
if Text.Start((Attribute),1)="Q" and Number.From(Text.Middle((Attribute),1,1))<5 then Text.Start((Attribute),2) else "Total")
Christian Neuberger ha utilizzato questa formula per ottenere il nome del dipendente, riempito e quindi filtrare la colonna 1 per includere solo Q1, Q2, Q3 o Q4. Anche Oz Du Soleil ha utilizzato questo metodo.
Ken Puls, MVP di Excel, probabilmente vince con la sua formula. Cerca un trattino basso per sapere se questo non è il nome del dipendente.
Guarda la soluzione completa di Ken in Excel MVPs Attack the Data Cleansing Problem in Power Query.
Torna alla pagina principale per la sfida Podcast 2316.
Leggi il prossimo articolo di questa serie: Power Query: gestione di più intestazioni identiche.
