Power Query: использование предложений Else If в условных столбцах - Советы по Excel

Содержание

Заметка

Это одна из серии статей, в которых подробно описаны решения, присланные для задачи Podcast 2316.

В моем решении по изменению формы данных мне нужен был способ узнать, содержит ли столбец имя сотрудника или значение, такое как Q1, Q2, Q3, Q4. В моем решении я предполагал, что ни у кого не будет имени из двух символов, поэтому я добавил столбец для вычисления длины текста в столбце.

Джейсон М избавился от необходимости в столбце Length, добавив три предложения Else If в свой Conditional Column.

Добавить условный столбец

Затем условное вычисление для Employee ищет для Quarter значение Null: if (Quarter) = null then (Category Description) else null.

Условный расчет

Вот M-код Джейсона:

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"

Ондржей Малински прислал решение, в котором также использовалось несколько предложений Else If:

Множественный else-if

Мэтью Уайкл прислал решение с еще одним способом идентифицировать помещения. Его метод проверяет, что текст начинается с Q и вторая цифра меньше 5:

if Text.Start((Attribute),1)="Q" and Number.From(Text.Middle((Attribute),1,1))<5 then Text.Start((Attribute),2) else "Total")

Определить кварталы

Кристиан Нойбергер использовал эту формулу, чтобы получить имя сотрудника, заполнить, а затем отфильтровать столбец 1, чтобы включить только Q1, Q2, Q3 или Q4. Оз Дю Солей также использовал этот метод.

Отфильтрованный столбец

Лучший игрок в Excel Кен Пульс, вероятно, выиграет благодаря своей формуле. Он ищет подчеркивание, чтобы узнать, не является ли это именем сотрудника.

См. Полное решение Кена на странице Excel MVPs Attack the Data Cleansing Problem в Power Query.