Екцел МВП-ови нападају проблем чишћења података у Повер Куери-ју - Екцел савети

Белешка

Ово је један од низа чланака који детаљно описују решења послана за изазов Подцаст 2316.

Екцел МВП Оз Ду Солеил са канала Екцел он Фире на ИоуТубе-у поменуо је бразилског јахача бикова Каикуе Пацхецхо. Оз је прва особа која је приметила да сам кренуо спорим путем додајући четири четвртине.

Озов видео је:
хттпс : //ввв.иоутубе.цом/ватцх?в=ОлуЗлФ44ПНИ

Његов код је:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

Још једно решење, ово од Екцел МВП-а Јохн МацДоугалл.

  • Јохн је први рекао да брисањем два додатна корака додата Повер Куери уклањате непарне суфиксе у дупликатима К1 К2 К3 К4.
  • Џон је рано користио индексну колону која би се користила на крају за сортирање. Али - Џон је повезао своју индексну колону након описа категорије. Користио је карактер вертикалне цеви | како би касније могао да разбије податке.
  • Јохн је своју условну колону откуцао као прилагођену колону, уместо да користи интерфејс условне колоне.
Условна колона као прилагођена колона

Погледајте Јохнов видео овде:
хттпс : //ввв.иоутубе.цом/ватцх?в=Дкмб6СЕЈДКСИ

Екцел МВП Кен Пулс, коаутор књиге М ис фор (Дата) Монкеи, послата у три решења. Његова условна колона је вероватно најкраћа.

Али Кеново преферирано решење игнорише првобитно питање. Уместо да креира табелу у Повер Куери-у, он креира покретни скуп података у Повер Куери-у, а затим завршава са изведеном табелом.

Посљедњи преглед Кена у Повер Куери-у изгледа овако:

Збирни скуп података

Ево Кеновог кода:

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))), #"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.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Након што креира овај упит само као везу, он затим користи пивот табелу за креирање коначног извештаја.

Завршни извештај са пивот табелом

Решења из других МВП-а:

  • Код Вин Хопкинс-а је овде: Повер Куери: Суочавање са више идентичних заглавља.
  • Код Мике Гирвина је овде: Повер Куери: Извлачење левих 2 знака из колоне.
  • Овде је решење формуле Рогера Говиера: Формула Солутионс.

Вратите се на главну страницу за изазов Подцаст 2316.

Прочитајте следећи чланак из ове серије: Повер Куери: Изнад корисничког интерфејса: Табле.Сплит и још много тога.

Занимљиви Чланци...