Вставка текста вручную в пользовательский столбец и должна быть видна при обновлении отчета

У нас есть таблица, показывающая такие данные, как OpprId, доход, регион, где таблица сортируется на основе наибольшего дохода. Эта таблица была построена с использованием силового запроса. Здесь пользователь хочет добавить новый столбец, сделать его столбцом статуса и сохранить статус. Этот статус должен отображаться при обновлении соединения с отчетом. Так есть ли какая-либо формула столбца для сохранения введенного текста, и этот статус должен быть в раковине с OppId как в будущем могут быть добавлены новые OppId, поэтому порядок сортировки может измениться.

OppId   ServiceLineServiceRevenueCUS    Status 
1       101584729.9                     Good
2       62272199                        let u know
3       11000000                        dfghd
4       9000000                         fdgdf
5       8200000                         fdgf 
6       7500000                         fgdf 
7       6000000                         fgdf
8       5650000
Источник

2 ответа

Изменить 29/11/2016: видео теперь доступно, чтобы прояснить это решение. Код немного отличается от кода ниже; в основном это все то же решение.

На другом форуме я ответил на аналогичный вопрос. Сначала входные данные (имя и фамилия) были прочитаны и выведены с помощью запроса Power Query. К этому выводу был добавлен столбец (Возраст - поддерживается вручную). Теперь я создал запрос, который считывает данные из таблицы ввода, соединяет слева с таблицей вывода и записывает результаты обратно в таблицу вывода. Таким образом, таблица Output является как входом, так и выходом этого запроса.

let
    Source1 = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    Typed1 = Table.TransformColumnTypes(Source1,{{"Last Name", type text}, {"First Name", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Output"]}[Content],
    Typed2 = Table.TransformColumnTypes(Source2,{{"Last Name", type text}, {"First Name", type text}, {"Age" , type number}}),
    Renamed2 = Table.RenameColumns(Typed2,{{"Last Name", "Last"}, {"First Name", "First"}}),
    Join1and2 = Table.Join(Typed1,{"Last Name", "First Name"},Renamed2,{"Last", "First"}, JoinKind.LeftOuter),
    Removed = Table.RemoveColumns(Join1and2,{"Last", "First"})
in
    Removed
Источник
  • Марсель Буг проделал большую работу, найдя решение и потратив время на редактирование видео с примером.
  • После положительного тестирования, вот мой перефраз его объяснений с большим количеством письменных деталей, чтобы, надеюсь, помочь людям, еще не очень знакомым с Excel Power Query.

Ситуация:

  • Книга Excel с существующим листом (например, "SQLdbOrigin"), содержащим данные исходного запроса SQL.
  • Вы хотите добавить к этим исходным данным дополнительные столбцы с введенными вручную значениями, сохраняя их связь с запрашиваемыми данными при обновлении исходных данных SQL-запроса.

Порядок действий:

  1. Из ячейки в существующей исходной таблице данных (полученной, например, из запроса БД) Power запрашивает эту таблицу в новой выходной таблице:
    • Меню: "Данные> Из таблицы" (исходной таблице данных будет автоматически присвоено имя "Таблица1")
    • Откроется окно редактора Power Query Editor с запрошенными данными.
    • В "Примененных шагах": переименуйте "Измененный тип" (через F2), например, в "SQLdata", а имя запроса - на "SQLoutput".
    • Нажмите на меню "Закрыть и загрузить", чтобы автоматически создать новый лист с выходной таблицей "SQLoutput".
    • При желании можно переименовать лист и таблицу
  2. Добавьте (строки за пределами таблицы, форматы везде и) столбцы для ручных полей, прикрепленных к 'SQLoutput', и заполните данные
    • NB: определение таблицы будет автоматически настроено для включения этих новых столбцов.
  3. Из ячейки в таблице SQLoutput Power запросит эту таблицу выходных данных:
    • Меню: "Данные> Из таблицы"
    • В "Примененных шагах": переименуйте "Измененный тип" (через F2), например, в "FullData", а имя запроса - на "SQLoutput".
  4. Нажмите "Расширенный редактор", откроется новое окно для SQLoutput (2).
    • Скопируйте 2 строки между let и in
    • Нажмите "Готово".
    • Закройте окно редактора Power Query и нажмите "Отменить".
  5. В столбце "Запросы к книге" щелкните правой кнопкой мыши запрос "SQLoutput"> "Изменить".
  6. В этом окне редактора Power Query измените запрос SQLoutput для слияния с его собственной выходной таблицей FullData:
    • Нажмите "Расширенный редактор".
    • Добавьте запятую в конце строки непосредственно перед 'in'
    • Вставьте перед строкой с 'in' две ранее скопированные строки (из запроса Table1_2)
    • Переименуйте "Source" в "Source2" в 2 новых добавленных строках.
    • Переименуйте таблицу после 'in' из 'SQLdata' в 'FullData' (как было выбрано ранее)
    • Нажмите "Готово" (и обратите внимание на столбец "Параметры запроса" справа).
    • Нажмите "Объединить запросы".
    • В раскрывающемся списке выберите (единственно возможную) таблицу SQLoutput (Current).
    • Выберите ключевые столбцы, соответствующие в двух таблицах.
    • Выберите "Присоединиться к виду": "Левый внешний".
    • Нажмите "ОК".
    • В строке формул редактора Power Query (при необходимости установите флажок в Меню> Просмотр):
      • Переименуйте первую "FullData" в "SQLdata" (чтобы переопределить две таблицы для объединения)
    • В заголовке нового крайнего правого столбца щелкните значок "развернуть" "<>".
      • Снимите первый флажок "(Выбрать все столбцы)"
      • Выберите в списке добавленные столбцы (с ручными значениями)
      • Снимите флажок "Использовать исходное имя столбца в качестве префикса".
      • Нажмите "ОК".
    • Меню: "Закрыть и загрузить"
  7. Контрольная работа:
    • Добавить и / или удалить строку (строки) в таблице листа SQLdbOrigin, чтобы смоделировать изменение в базе данных
    • Обновите запрос (щелкнув правой кнопкой мыши в таблице; "Запросы в книге" справа можно закрыть)
    • Обратите внимание, что результаты в порядке
    • Улыбка.
  8. Примечания:
    • Чтобы вычисляемые ячейки сохранили свои функции, добавьте их в исходный лист SQLdbOrigin; Остерегайтесь: они будут вести себя так, как ожидалось, только для ячеек в исходной таблице.
    • Чтобы вычисляемые ячейки работали правильно в таблице вывода SQL, сначала добавьте пустой столбец справа от таблицы (который затем можно скрыть), а затем добавьте столбец (и) вычисляемых полей. Они не будут изменены при обновлении запросов и сохранят свои функции неизменными (конечно, для относительных адресов)
    • Не переименовывайте заголовки столбцов в исходной таблице Table1 на листе SQLdbOrigin, поскольку они используются в запросах, если вы не обновите все запросы соответствующим образом...
    • Также будьте осторожны, чтобы не изменить имена запросов или таблиц по той же причине...
    • Форматирование таблицы вывода SQL, кажется, сохраняется при обновлении, за исключением того, что высота строки всегда сбрасывается.
Источник

Если я правильно понимаю ваш вопрос, вы хотите сгенерировать таблицу с помощью Power Query, сделать так, чтобы пользователь вводил информацию в ячейку, а затем каждый раз, когда вы обновляете пользовательскую ячейку, не перезаписывается?

Пока введенные пользователем ячейки не являются частью столбцов, загружаемых Power Query, это должно "просто работать" сегодня. Если вы измените ячейки в таблице Power Query, они будут перезаписываться при каждом обновлении.

Это не тот вопрос, который вы задали, но, похоже, вы захотите использовать Microsoft PowerApps для создания этого отчета. Power Query не собирается синхронизировать разные версии одного и того же файла...

Отказ от ответственности: я работаю в Microsoft, в той же бизнес-группе, что и PowerApps.

Источник
Другие вопросы по тегам