Итоги строк на основе имени столбца в PowerQuery

У меня есть файл данных, содержащий около 400 столбцов. Мне нужно импортировать эти данные в PowerPivot. Чтобы уменьшить размер файла, я хотел бы использовать PowerQuery для создания двух разных итоговых значений строк, а затем удалить все ненужные столбцы при загрузке.

В то время как мой столбец итоговой суммы первой строки (RowTotal1) суммирует все 400 столбцов, я также хотел бы получить итоговую сумму второй строки (RowTotal2), которая вычитает из RowTotal1 любой столбец, имя которого содержит текст «щелчок» в нем.

Во-вторых, я хотел бы использовать значение в моем столбце Country в качестве переменной, чтобы также вычесть любой столбец, содержащий эту переменную. например

Сайт ---- Страна ---- Col1 ---- Col2 ---- ClickCol1 ---- Col3 ---- Германия ---- RowTotal1 ---- RowTotal2

1a -------- США ---------- 2 --------- 4 ----------- 8 ------- ----- 16 ---------- 24 -------------- 54 --------------- 46-- -----

2a ----- Германия ------- 2 --------- 4 ----------- 8 ------------ 16 ---------- 24 -------------- 54 --------------- 22 -------

RowTotal1 = 2 + 4 + 8 + 16 + 24

RowTotal2 (первая строка) = 54–8 (ClickCol1)

RowTotal2 (вторая строка) = 54–24 (Германия) - 8 (ClickCol1)

Это возможно? (ИЗМЕНИТЬ: да. См. ответ ниже)

ПЕРЕСМОТРЕННЫЙ ВОПРОС: Есть ли более эффективный способ использования памяти, чем попытка сгруппировать более 300 миллионов строк за один раз?


excel powerquery
person Chris    schedule 06.04.2016    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Код будет выглядеть примерно так:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site", type text}, {"Country", type text}, {"Col1", Int64.Type}, {"Col2", Int64.Type}, {"ClickCol1", Int64.Type}, {"Col3", Int64.Type}, {"Germany", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Country", "Site"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Value2", each if [Country] = [Attribute] or [Attribute] = "ClickCol1" then 0 else [Value] ),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Site", "Country"}, {{"RowTotal1", each List.Sum([Value]), type number},{"RowTotal2", each List.Sum([Value2]), type number}})
in
    #"Grouped Rows"

Но поскольку у вас много столбцов, я должен объяснить шаги:

  • (Предполагая, что они есть в файле Excel) Импортируйте их в Power Query
  • Выберите столбцы «Сайт» и «Страна» (с помощью Ctrl), щелкните правой кнопкой мыши> Отменить сводку других столбцов.
  • Добавьте столбец с помощью этой формулы (вам может потребоваться расширенный редактор): Table.AddColumn (# «Другие столбцы без преобразования», «Значение2», каждый, если [Страна] = [Атрибут] или [Атрибут] = «ClickCol1», то 0 иначе [Ценить])
  • Выберите столбцы сайта и страны, щелкните правой кнопкой мыши> Группировать по
  • Сделайте это так: введите описание изображения здесь
person Oğuz Yıldız    schedule 06.04.2016
comment
Привет, спасибо за ответ. Просто пришел к такому же выводу и собирался обновить сообщение. Единственная проблема, с которой я столкнулся, заключается в том, что каждый месяц без поворота содержит около 16 миллионов строк. В общей сложности мой анализ данных занимает около 24 месяцев. В то время как PowerQuery быстро отключает сворачивание, группировка около 380 миллионов строк по 6 столбцов превышает это время. Я проголосую за ваш ответ, поскольку технически он отвечает на вопрос, но оставлю ответ нерешенным, поскольку я надеюсь на более эффективный с точки зрения памяти способ сделать это. (Excel x64 с 8 ГБ ОЗУ) - person Chris; 06.04.2016
comment
Я не уверен, что это можно сделать быстрее ... Возможно, вы можете определить функцию, которая будет делать это, но с некоторыми фильтрами. Или вы можете сгруппировать элементы на большом шаге, чтобы сделать внутренние шаги меньше. Например, вы могли бы сгруппировать строки на внешнем уровне для каждого месяца, например? - person Oğuz Yıldız; 07.04.2016
comment
(вы бы выбрали операцию All Rows, когда группируете месяцы на внешней стороне) - person Oğuz Yıldız; 07.04.2016
comment
Хммм, все хорошие идеи. К сожалению, мне пока не очень удобно создавать собственные функции в M, но я попробую. В качестве альтернативы, группировка по более крупным шагам имеет большой смысл. Встряхну дерево (так сказать) и посмотрим, что выйдет. Спасибо еще раз! - person Chris; 08.04.2016
comment
Этот blogs.msdn.microsoft.com/ mvpawardprogram / 2013/08/19 / имеет хорошее объяснение и пример для создания ваших собственных функций M :) - person Oğuz Yıldız; 09.04.2016