Заметка
Это одна из серии статей, в которых подробно описаны решения, присланные для задачи Podcast 2316.
В моем исходном видео о изменении формы данных я подошел к моменту, когда мне нужно было получить первые 2 символа из столбца. Мой метод включал разделение столбца для генерации первых двух символов и всего остального. Затем я удалил все остальное.
Майк Гирвин из канала ExcelisFun, а также Брэндон Бримберри, Вин Хопкинс из Access Analytic, Герт Демулле отметили, что более быстрым способом было бы использовать преобразование, извлечение, первые символы.
При этом сохраняется только информация о квартале, без необходимости удалять остальную часть столбца.
Богдан Дуда использовал извлеченный текст перед разделителем, чтобы получить все до символа _.
Майк Гирвин отметил, что его решение было «на лету, быстро, прежде чем я выйду за дверь». Вот его код:
let Source = Excel.CurrentWorkbook()((Name="BadStartData"))(Content), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, ("Category Description"), "Quarter", "Amount"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if not Text.StartsWith((Quarter),"Q") then (Quarter) else null), #"Filled Down" = Table.FillDown(#"Added Custom",("Employee")), #"Extracted First Characters" = Table.TransformColumns(#"Filled Down", (("Quarter", each Text.Start(_, 2), type text))), #"Filtered Rows" = Table.SelectRows(#"Extracted First Characters", each Text.StartsWith((Quarter), "Q")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Quarter)), "Quarter", "Amount", List.Sum), #"Filtered Rows1" = Table.SelectRows(#"Pivoted Column", each ((Employee) "Dept. Total")), #"Inserted Sum" = Table.AddColumn(#"Filtered Rows1", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee", Order.Ascending),("Category Description", Order.Ascending))), #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",(("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", type number), ("Total", type number), ("Employee", type text), ("Category Description", type text))) in #"Changed Type"Вот решение Гирта ДеМюлла:
let Source = Excel.CurrentWorkbook()((Name="tblUglyData"))(Content), #"Changed Type" = Table.TransformColumnTypes(Source,(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q12", type number), ("Q23", type number), ("Q34", Int64.Type), ("Q45", Int64.Type), ("Employee 2", Int64.Type), ("Q16", Int64.Type), ("Q27", Int64.Type), ("Q38", Int64.Type), ("Q49", Int64.Type), ("Employee 3", Int64.Type), ("Q110", Int64.Type), ("Q211", Int64.Type), ("Q312", Int64.Type), ("Q413", Int64.Type), ("Employee 4", type number), ("Q114", type number), ("Q215", type number), ("Q316", type number), ("Q417", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.StartsWith((Attribute), "Employee") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each Text.StartsWith((Attribute), "Q")), #"Extracted First Characters" = Table.TransformColumns(#"Filtered Rows", (("Attribute", each Text.Start(_, 2), type text))), #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters",(("Attribute", "Quarter"))), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"(Quarter)), "Quarter", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",(("Employee", type text))), #"Inserted Sum" = Table.AddColumn(#"Changed Type1", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee", Order.Ascending), ("Category Description", Order.Ascending))) in #"Sorted Rows"Вернитесь на главную страницу испытания «Подкаст 2316».
Прочтите следующую статью этой серии: Power Query: добавление столбца итогов.
