{"draftJsState":{"blocks":[{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"asu8r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Добрый день дорогие читатели и коллеги. ","type":"unstyled","key":"1lch6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Не давно от одного из заказчиков поступил вопрос, как корректно добавить в отчет PowerBI многострочное текстовое поле проекта? Многострочное поле в БД данных ProjectServer и OData храниться как текст с HTML разметкой. Рассмотрим на примере поля карточки проекта Цель (по SMART).","type":"unstyled","key":"7ekgv"},{"data":{"image":{"id":"5d0a1e64721e9100ae8e2db5"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"d7rjk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"bsshd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Как мы видим при выборке из БД Project Server и OData поле содержит гипертекстовую разметку и при добавлении в отчет отображается не «красиво».","type":"unstyled","key":"7u87r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"66i0n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Пример данных из OData","type":"unstyled","key":"43lco"},{"data":{"image":{"id":"5d0a1e7822f45600b02af312"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"3kr5v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"bqmre"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"Пример данный из БД","type":"unstyled","key":"f1k8r"},{"data":{"image":{"id":"5d0a1e8cf732bd00b15517ed"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"d25af"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"9bo6j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Далее мы рассмотрим 2 способа отображения корректной информации в PowerBI:","type":"unstyled","key":"5lu5a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":0,"style":"BOLD"}],"text":"Способ 1. Очистка поля от не нужных HTML тэгов. Для этого я буду использовать стандартную функцию «Заменить» в Power Query:","type":"unstyled","key":"629ut"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":0,"style":"BOLD"},{"length":11,"offset":10,"style":"BOLD"}],"text":"Скрипт до модификации:","type":"unstyled","key":"9c3h9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"let","type":"unstyled","key":"4qdhv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Источник = OData.Feed(\"http://epm2016/PWAMSCONF/_api/ProjectData/\"),","type":"unstyled","key":"3hlv7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проекты_table = Источник{[Name=\"Проекты\",Signature=\"table\"]}[Data],","type":"unstyled","key":"bgtd1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Удаленные столбцы\" = Table.RemoveColumns(Проекты_table,{\"ОписаниеТипаКорпоративногоПроекта\", \"ИдентификаторТипаКорпоративногоПроекта\"}),","type":"unstyled","key":"2qgv5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Другие удаленные столбцы\" = Table.SelectColumns(#\"Удаленные столбцы\",{\"ИДПроекта\", \"ИмяПроекта\", \"Целипроекта\"}),","type":"unstyled","key":"agla6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Дублированный столбец\" = Table.DuplicateColumn(#\"Другие удаленные столбцы\", \"Целипроекта\", \"Копия Целипроекта\"),","type":"unstyled","key":"8feg2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Переименованные столбцы\" = Table.RenameColumns(#\"Дублированный столбец\",{{\"Копия Целипроекта\", \"ЦелипроектаNOHTML\"}})","type":"unstyled","key":"3ftoi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"in ","type":"unstyled","key":"58bof"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Переименованные столбцы\"","type":"unstyled","key":"8a5ob"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"},{"length":11,"offset":13,"style":"BOLD"}],"text":"Скрипт после модификации:","type":"unstyled","key":"3j7o3"},{"data":{"image":{"id":"5d0a1e9ed46fab00b09a0c6e"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"f0d8j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"2nlfb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"},{"length":12,"offset":22,"style":"BOLD"}],"text":"Листинг скрипта после модификации:","type":"unstyled","key":"7m47i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"let","type":"unstyled","key":"796vn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Источник = OData.Feed(\"http://epm2016/PWAMSCONF/_api/ProjectData/\"),","type":"unstyled","key":"e04qh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проекты_table = Источник{[Name=\"Проекты\",Signature=\"table\"]}[Data],","type":"unstyled","key":"f5bjk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Удаленные столбцы\" = Table.RemoveColumns(Проекты_table,{\"ОписаниеТипаКорпоративногоПроекта\", \"ИдентификаторТипаКорпоративногоПроекта\"}),","type":"unstyled","key":"c23nl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Другие удаленные столбцы\" = Table.SelectColumns(#\"Удаленные столбцы\",{\"ИДПроекта\", \"ИмяПроекта\", \"Целипроекта\"}),","type":"unstyled","key":"flfb5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Дублированный столбец\" = Table.DuplicateColumn(#\"Другие удаленные столбцы\", \"Целипроекта\", \"Копия Целипроекта\"),","type":"unstyled","key":"alabh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"Переименованные столбцы\" = Table.RenameColumns(#\"Дублированный столбец\",{{\"Копия Целипроекта\", \"ЦелипроектаNOHTML\"}}),","type":"unstyled","key":"ej9vn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"ЗаменаHTML

\" = Table.ReplaceValue(#\"Переименованные столбцы\",\"

\",\" \",Replacer.ReplaceText,{\"ЦелипроектаNOHTML\"}), ","type":"unstyled","key":"8ccqj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"ЗаменаHTML

\" = Table.ReplaceValue(#\"ЗаменаHTML

\" ,\"

\",\" \",Replacer.ReplaceText,{\"ЦелипроектаNOHTML\"}), ","type":"unstyled","key":"74aom"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"ЗаменаHTML \" = Table.ReplaceValue(#\"ЗаменаHTML

\",\" \",\" \",Replacer.ReplaceText,{\"ЦелипроектаNOHTML\"}), ","type":"unstyled","key":"cpg9u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"ЗаменаHTML

\" = Table.ReplaceValue(#\"ЗаменаHTML \",\"
- \",\" \",Replacer.ReplaceText,{\"ЦелипроектаNOHTML\"}), ","type":"unstyled","key":"6mfg7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"ЗаменаHTML
- \" = Table.ReplaceValue(#\"ЗаменаHTML
  - \",\"
  - \",\", \",Replacer.ReplaceText,{\"ЦелипроектаNOHTML\"}), ","type":"unstyled","key":"e2esk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"ЗаменаHTML
  \" = Table.ReplaceValue(#\"ЗаменаHTML
- \",\"
\",\" \",Replacer.ReplaceText,{\"ЦелипроектаNOHTML\"}) ","type":"unstyled","key":"6413d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"in ","type":"unstyled","key":"dd766"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#\"ЗаменаHTML

\"","type":"unstyled","key":"d5bdc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Я включил только некоторые из HTML-тегов, по аналогии вы можете добавить остальные это достаточно легко сделать. Это довольно быстрый и простой способ, так же вы можете сделать данный способ массовым для всех столбцов и тегов HTML, создав свою собственную функцию, если вам нужно.","type":"unstyled","key":"59ftc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"4jv1j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"},{"length":7,"offset":31,"style":"BOLD"},{"length":12,"offset":39,"style":"BOLD"}],"text":"Способ 2. Использование Custom Control из магазина.","type":"unstyled","key":"7hbt1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Первый способ достаточно простой если нам не нужно сохранять форматирование (использование в табличных представлениях), но что делать если нам необходимо сохранить условное форматирование в отчете?","type":"unstyled","key":"2f9n6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":43,"style":"BOLD"}],"text":"Для этого я использую визуальный компонент HTML Viewer","type":"unstyled","key":"62f3s"},{"data":{"image":{"id":"5d0a1ebbda601b00afa9274c"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"elgis"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"23eju"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"После добавления из магазина визуальный компонент станет доступен в панели визуализации.","type":"unstyled","key":"ejsh1"},{"data":{"image":{"id":"5d0a1ecbb1dd5500afc2f78b"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"c43fb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ","type":"unstyled","key":"fi7hs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"}],"text":"Результат. ","type":"unstyled","key":"4vgk3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"fg8t0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"marker","key":"pBXjcNNKtdxCwh6iuYQw"}],"entityMap":{}},"mentions":[]}