PowerQuery (M): Как извлечь дату из большого текстового поля?

В моей таблице есть текстовый столбец Remarks, который обычно содержит большой объем текста.

Вот пример:
24 марта 2017 г., 11:14:41 - EMD ДЛЯ СТАТУСА NFU 30 марта 2017 г.
30 марта 2017 г. 10:58:03 AM - CLD НА РЕЦЕПЦИОНАТ GM НЕДОСТУПЕН NFU, 13 апреля 2017 г.
13 апреля 2017 г. 11:10:15 AM - CLD НА РЕЦЕПЦИОНЕР ПРЕДОСТАВЛЯЕТ ИНФОРМАЦИЮ NFU4 / 27
27 апреля 2017 г. 9:02:20 - MLD INV С ШЕСТИГРАННИКОМ 90 ДНЕЙ
27 апреля 2017 г. 9:15:03 - ЗА ОТВЕТ ПОЗВОНИТ КЛИЕНТУ ДЛЯ ОПЛАТЫ < br> 27 апреля 2017 г. 11:03:46 - NFU 5/5 PER REP CUSTOMER CONFUSION
5/5/2017 8:55:17 AM - NFU 5 / 9/2017 CRP PER REP CHECK БЫЛ MLD 02.05.17

Весь этот текст будет втиснут в одно поле, и мне нужно извлечь последнюю дату NFU из поля для использования в расчетах и ​​фильтрации.

В приведенном выше примере я хотел бы извлечь дату 09.05.2017 из последней строки.
Но, как видите, дата может быть в любом формате в любом месте поля. .

Я предполагаю, что Excel может преобразовать текст в значение даты в любом из вышеперечисленных форматов (если нет, я займусь этим другим способом - обучением сотрудников и т. Д.)

Главное, что мне нужно выяснить, как делать с помощью PowerQuery:

  • Найдите последний экземпляр "NFU" в этом поле.
  • Извлеките весь текст непосредственно после последнего экземпляра «NFU», включая пробел между «NFU» и датой, если таковая имеется.
    На этом этапе результат должен быть:
    " 5/9/2017 CRP PER REP CHECK WAS MLD 5/2/17"
  • Удалите белые пятна в начале строки.
    На этом этапе результат должен быть следующим:
    "5/9/2017 CRP PER REP CHECK WAS MLD 5/2/17"
  • Найдите первый символ, отличный от 0-9, / или - (или конца строки, в зависимости от того, что наступит раньше)
  • При необходимости обрежьте строку до первого символа, отличного от даты.
    На этом этапе результат должен быть следующим:
    "5/9/2017"
  • Наконец, попытайтесь отформатировать полученный текст в Date тип / формат и вернуть как результат для настраиваемого столбца PowerQuery.

Глядя на доступные строковые функции PowerQuery, я не уверен, возможно ли это вообще.


string excel powerquery m excel-2016
person Giffyguy    schedule 11.05.2017    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Я полагаю, вы имеете в виду функции Текст в Power Query. Они действительно несколько ограничены, но в библиотеке функций Power Query есть множество других опций: в этом случае на помощь могут прийти функции List.

Между прочим: я проверил "NFU", чтобы избежать "CO NFU SION" (предпоследняя строка в ваших примерах).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"example", type text}}),
    LastNFU = Table.AddColumn(Typed, "LastNFU", each Text.PositionOf([example]," NFU",Occurrence.Last), Int64.Type),
    AfterNFU = Table.AddColumn(LastNFU, "AfterNFU", each if [LastNFU] = -1 then null else Text.Range([example],[LastNFU]+4)),
    Trimmed = Table.TransformColumns(AfterNFU,{{"AfterNFU", Text.Trim}}),
    TextToList = Table.TransformColumns(Trimmed,{{"AfterNFU", each if _ = null then {} else Text.ToList(_)}}),
    ListFirstN = Table.TransformColumns(TextToList,{{"AfterNFU", each List.FirstN(_, each Text.Contains("01234567890-/",_))}}),
    TextCombine = Table.TransformColumns(ListFirstN, {"AfterNFU", Text.Combine, type text}),
    Date = Table.TransformColumnTypes(TextCombine,{{"AfterNFU", type date}}, "en-US"),
    Renamed = Table.RenameColumns(Date,{{"AfterNFU", "Date"}}),
    Removed = Table.RemoveColumns(Renamed,{"LastNFU"})
in
    Removed
person MarcelBeug    schedule 11.05.2017
comment
Это потрясающе. Я немного изменил ваше решение и объединил его в единую (нечитаемую :) формулу: if [Remarks]<>null then if Text.PositionOf([Remarks],"NFU",Occurrence.Last)>=0 then Text.Combine(List.FirstN(Text.ToList(Text.Trim(Text.Range([Remarks],Text.PositionOf([Remarks],"NFU",Occurrence.Last)+3))),each Text.Contains("0123456789/-",_)),"") else Date.From(DateTime.LocalNow()) else Date.From(DateTime.LocalNow()) - person Giffyguy; 11.05.2017
comment
Остается только один вопрос: как я могу определить, не удалось ли преобразовать его в тип date? Если он не может проанализировать ее как дату, я хочу отображать текущую дату по умолчанию. - person Giffyguy; 11.05.2017
comment
Если его нельзя проанализировать как дату, Table.TransformColumnTypes выдаст ошибки, которые можно заменить текущей датой. - person MarcelBeug; 11.05.2017

arrow_upward
-1
arrow_downward

Простая формула, такая как = RIGHT (A1, LEN (A1) - (FIND ("NFU", A1,1) -1)), будет работать для извлечения строки рядом с NFU. Предполагая, что текст находится в ячейке A1. Но необходимо более детально изучить другие требования.

person Apurv Pawar    schedule 11.05.2017