Вместо вступления выражаю надежду
Идея для данной работы вынашивалась какое-то время. Ранее не хватало навыков и знаний, но задавшись целью в течении года (52 недели по 16 часов работы = 832 часа (примерно, конечно же)) были изучены 2 инструмента работы с большими данными и методами построения отчётов. Увиденный вами результат - следствие кропотливой работы, которая, в силу обстоятельств, описываемых ниже, скорее всего на этом и остановится. Однако! Вдруг кем-то будет замечена, получено продолжение и вырастет в нечто замечательное (надеюсь, что и я вместе с ней).
Все представленные данные отныне являются полностью открытыми и каждый сможет повторить сделанное или взять уже готовое. Проделать данную работу мне не удалось бы без стимулирующего фактора - студентов, жаждущих новых знаний и вынуждающих двигаться вперёд, чтобы не выглядеть некрасиво. Также следует отметить, что и без технических возможностей Пермского национального исследовательского политехнического университета я бы также не обошёлся, поскольку домашних вычислительных мощностей просто не хватило бы.
Итоговое решение я выкладываю на своём канале в Telegram, включая ссылки для скачивания баз-данных (вдруг пропадут). Искренне надеюсь, что для читающих не составит труда подписаться ну и поддержать меня и мой труд.
Кому будет интересна данная статья?
В сети, лично для меня, было достаточно трудно искать решение какой-либо сквозной задачи с применением Power BI. Т.е. есть отдельные частные примеры, есть учебники и видеоуроки и в целом много материала. Однако, достаточно мало полноценных мастер-классов, где даётся разъяснение всех этапов выполнения задания. По этой причине первая группа заинтересованных - те, кто ищет подобный кейс с достаточно обывательским рассказом о причинах свершения тех или иных шагов.
Вторым заинтересованным лицом можно обозначить человека, имеющего отношение к миру финансов, анализу деятельности предприятий, кредитующих организаций. Продукт нацелен на представление информации о деятельности "совокупности" предприятий, что может помочь аналитику оценить состояние этой самой совокупности, детализировать отдельные её части и, наконец-то, осознать, что общепринятые коэффициенты оценки финансового состояния, методы оценки, зачастую, неприменимы к части субъектов рынка. Но об этом позже.
В качестве третьего заинтересованного можно выделить учёного-экономиста, аналитика, разрабатывающего какие-либо актуальные подходы к оценке финансового состояния предприятия, отрасли, региона.
Отдельно хочется обратить внимание на данную работу участников экономики, принимающих решения в части поддержки, финансирования, развития и т.д. Возможно для вас данный материал послужит основой для анализа и принятия решений.
Почему на этом этапе данная работа может увянуть?
Всё просто. Приложение выстраивается на открытых данных, предоставляемых сайтом Федеральной службы государственной статистики. На момент написания статьи данные находились по данной ссылке. Если посмотреть, то 2018 - последний год, когда данные присутствуют. В дальнейшем было принято решение распространять массив данных по подписке, о чём свидетельствует данный ресурс и данная запись на сайте Федеральной налоговой службы. Чтобы получить свежий массив данных нужно уплатить 200 т.р. Очень хитрая манипуляция) Т.е. данные, вроде как, распространяются бесплатно, т.е. можно посмотреть информацию про любого агента, но вот получить массив - это уже по подписке.
Надо сказать , что модель бизнеса очень прикольная. Получается, что ФНС обязала всех проверять своих контрагентов, в рамках проведения мероприятий по налоговому контролю в отношении соблюдения принципа должной осмотрительности и осторожности при выборе контрагента, используя открытые источники. Наплодились сайты, где формируется сводка по предприятиям, а потом ФНС взяла и принудила их уплачивать абонентскую плату, а данные собирает в обязательном порядке и бесплатно. Отличная модель.
Поскольку вся работа выстраивается на голом энтузиазме и собственными силами, то, конечно же, выплачивать такие средства у меня возможности нет. Объяснить руководству ВУЗа необходимость такой покупки я тоже не смогу, поскольку мы на самоокупаемости и без продаж результатов не будет финансирования. Так что, если не найдётся помощь, то и работа останавливается на достигнутом.
Но уже и этого результата достаточно, чтобы интересно и плодотворно поработать.
Дисклеймер (отказ от ответственности за переработку данных)
Будучи преисполненным уверенности, что работа ФНС - крайне кропотливый труд, а работники и система педантичны и точны, вначале я решал возникающие перед моим взором задачи, как задачи, по управлению, с корректными данными. Однако, в какой-то момент наступило понимание, что база вся в "дырах" и ошибках. Когда эти ошибки накапливаются, то анализ становится невозможным.
Обнаруживается это элементарным суммированием всех балансов предприятий по статьям в группы и попыткой сопоставить дебет с кредитом. Когда сопоставление не пошло, то стало понятно, что ошибка именно в данных.
Чтобы не быть голословным, давайте посмотрим некоторые совершенно случайные примеры, обнаруживаемые на момент написания статьи.
Рассмотрим предприятие Общество с ограниченной ответственностью сельскохозяйственный производственный комплекс «Аргамак». Отчётность данного предприятия Вы можете запросить (смотрим 2018 год) на официальном сайте статистики по ИНН (0228005239) или на любом другом сайте, где представлена информация о предприятиях, например, здесь. Что же мы обнаруживаем при просмотре (сократив общий вид, чтобы уменьшить изображение)?
А обнаруживаем мы, что сумма по трём разделам баланса, предприятия (выделяются красным цветом) не соответствует проставленной сумме в итоговом Балансе (фиолетовая отметка).
В качестве второго примера можно представить ООО "ТАТТРАНСКОНТУР" (1650109381), где всё по тому же 2018 году обнаруживается подобная ситуация:
Здесь специально взяты различные сайты, где, в том числе, производится оценка финансово-хозяйственной деятельности предприятий (видимо на основе этих ошибочных данных) .
Мы не будем искать виноватого и пытаться судить о том, на каком этапе возникла данная проблема, однако, проводить задуманный анализ на этих данных было бы некорректно.
Я взял на себя смелость скорректировать балансы предприятий по определённой модели, за что прошу прощения. Мне пришлось пережить дискуссию с весьма уважаемыми датасаянтистами на эту тему, но вот на такой позиции я остался.
Особенности процесса импорта или "Мощный запрос"
(Power query)
Для дальнейшего изложения требуется ознакомиться с логикой создания приложения с применением Power BI:
Условно вся работа разделяется между Power Query и Power BI. PQ отвечает за создание соединений с данными, переработку до нужно вида и интеграцию результата в общую модель данных.
В Power BI производится структурирование данных, если они из разных источников, группировка, при необходимости производятся дополнительные вычисления, создание новых мер и таблиц данных, необходимых для нужного отображения данных и подготовка конечного вида отчёта, максимально презентабельного для данной задачи.
Исходи из сказанного, ранее произнесённая фраза "Я взял на себя смелость скорректировать балансы предприятий " правильно звучит так: "В процессе подготовки данных они были частично модифицированы без нарушения целостности".
После открытия Power BI самым целесообразным, по моему убеждению, является переход в редактор Power Query. Не смотря на то, что есть предподготовленные диалоги импорта данных, склоняю вас к написанию кода в "чистом" виде. Тем не менее должен сказать, что Power Query предлагает графический интерфейс для многих действий. Переход в PQ выглядит так:
После попадания в PQ можно сформировать необходимый запрос, воспользовавшись имеющимися диалогами, однако, моим предпочтением является создание пустого запроса. Поскольку любые действия в PQ - выполнение функции, то всё, что нам необходимо выполнить прописывается "ручками".
Вначале, поскольку все файлы будут лежать в одном месте, целесообразно прописать путь к файлам, который будет использоваться во всех запросах.
При выполнении работы соблюдалась логика, что при вводе запроса для каждого нового файла потребуется лишь изменить год.
let
#"Директория хранения файлов" = "\\ns\Public\Power BI\Data\"
in
#"Директория хранения файлов"Соответственно, прописав однажды путь, значительно упрощается жизнь. Сам запрос был назван FilesDirectory.
Далее формируются тексты запросов для каждого года. Надо отметить, что запрос для 2011 года выстраивается на данных 2012, поэтому ниже приведён отдельно:
2011
let
#"Год" = 2012,
#"Путь расположения файлов" = #"FilesDirectory",
#"Наименование файла данных" = Lines.ToText(
{
"data-20200331-structure-",
Number.ToText(#"Год"),
"1231"
},
"") as text,
#"Наименование файла структуры" = Lines.ToText(
{
"structure-",
Number.ToText(#"Год"),
"1231"
},
"") as text,
#"Расширение файла" = "csv",
#"Длина таблица данных" = 124,
#"Импорт_1" = Lines.ToText(
{
#"Путь расположения файлов",
#"Наименование файла данных",
".",
#"Расширение файла"
},
"") as text,
#"Данные_1" = Csv.Document(File.Contents(#"Импорт_1"),
[Delimiter=";",
Columns=#"Длина таблица данных",
Encoding=1251,
QuoteStyle=QuoteStyle.None]),
#"Импорт_2" = Lines.ToText(
{
#"Путь расположения файлов",
"Help",
".",
#"Расширение файла"
},
"") as text,
#"Расшифровка_1" = Csv.Document(File.Contents(#"Импорт_2"),
[Delimiter=";",
Columns=3,
Encoding=1251,
QuoteStyle=QuoteStyle.None]),
#"Расшифровка_2" = Table.RenameColumns(#"Расшифровка_1",{{"Column1", "Numer"}, {"Column2", "Name_RUS"}, {"Column3", "Name_ENG"}}),
#"Импорт_3" = Lines.ToText(
{
#"Путь расположения файлов",
#"Наименование файла структуры",
".",
#"Расширение файла"
},
"") as text,
#"Структура_1" = Csv.Document(File.Contents(#"Импорт_3"),
[Delimiter=",",
Columns=4,
Encoding=1251,
QuoteStyle=QuoteStyle.Csv]),
#"Структура_2" = Table.PromoteHeaders (#"Структура_1"),
#"Список_полей_1" = #"Структура_2"[#"field name"],
#"Список_полей_2"=List.FirstN(#"Список_полей_1", #"Длина таблица данных"),
#"Список_полей_3" = Table.ColumnNames(#"Данные_1"),
#"Список_полей_4" = List.Zip({#"Список_полей_3",#"Список_полей_2"}),
#"Данные_2" = Table.RenameColumns(#"Данные_1", #"Список_полей_4"),
#"Перечень стобцов" = Table.FromList(Table.ColumnNames(#"Данные_2")),
#"Перечень стобцов с фильтром" = Table.SelectRows(#"Перечень стобцов", each
(Text.Length([Column1]) =5) and (Text.At([Column1], 4)="3")),
#"Перечень стобцов с фильтром в виде списка" = #"Перечень стобцов с фильтром"[Column1],
#"Удаленные столбцы" = Table.RemoveColumns(#"Данные_2",#"Перечень стобцов с фильтром в виде списка", MissingField.Ignore),
#"Перечень стобцов новый" = Table.FromList(Table.ColumnNames(#"Удаленные столбцы")),
#"Перечень стобцов новый с фильтром" = Table.SelectRows(#"Перечень стобцов новый", each
(Text.Length([Column1]) =5) and (Text.At([Column1], 4)="4")),
#"Перечень стобцов новый с заменой" = Table.AddColumn(#"Перечень стобцов новый с фильтром", "Result", each Text.Combine({Text.RemoveRange([Column1],Text.Length([Column1])-1),"3"})),
#"Список_полей_6"=#"Перечень стобцов новый с заменой"[Result],
#"Список_полей_5" = List.Zip({Table.ToList(#"Перечень стобцов новый с фильтром"), #"Список_полей_6"}),
#"Удаленные столбцы новый" = Table.RenameColumns(#"Удаленные столбцы", #"Список_полей_5"),
#"Вид таблицы с добавленным годом" = Table.AddColumn(#"Удаленные столбцы новый" , "Year", each 2011),
#"Финальный вид таблицы" = Table.TransformColumns(
#"Вид таблицы с добавленным годом",
{
{
"okved",
each
Text.Insert(
(_),0,
(if #"Год" <= 2016
then "ОКВЭД: "
else "ОКВЭД2: ")
),
type text
}
}
),
//Блок конвертации по кода 383, 384 ...
lst1 = List.Skip(Table.ColumnNames(#"Финальный вид таблицы" ), 8),
lst=List.Range(lst1,0,58),
f=(x,y)=>List.Accumulate(lst,x,(s,c)=>Record.TransformFields(s,{c,(x)=>Number.From(x)*y})),
g=(x)=> f(x,Number.Power(10, (Number.FromText(x[#"measure"])-383)*3)),
tbl = Table.ToRecords(#"Финальный вид таблицы"),
trnsf= List.Transform(tbl,g),
#"Окончательный вид таблицы перед дополнительными вычислениями" = Table.FromRecords(trnsf),
//...
#"Измененный тип 1" =
Table.TransformColumnTypes(#"Окончательный вид таблицы перед дополнительными вычислениями",{
{"11103", Int64.Type},
{"11203", Int64.Type},
{"11303", Int64.Type},
{"11403", Int64.Type},
{"11503", Int64.Type},
{"11603", Int64.Type},
{"11703", Int64.Type},
{"11803", Int64.Type},
{"11903", Int64.Type},
{"11003", Int64.Type},
{"12103", Int64.Type},
{"12203", Int64.Type},
{"12303", Int64.Type},
{"12403", Int64.Type},
{"12503", Int64.Type},
{"12603", Int64.Type},
{"12003", Int64.Type},
{"16003", Int64.Type},
{"13103", Int64.Type},
{"13203", Int64.Type},
{"13403", Int64.Type},
{"13503", Int64.Type},
{"13603", Int64.Type},
{"13703", Int64.Type},
{"13003", Int64.Type},
{"14103", Int64.Type},
{"14203", Int64.Type},
{"14303", Int64.Type},
{"14503", Int64.Type},
{"14003", Int64.Type},
{"15103", Int64.Type},
{"15203", Int64.Type},
{"15303", Int64.Type},
{"15403", Int64.Type},
{"15503", Int64.Type},
{"15003", Int64.Type},
{"17003", Int64.Type},
{"21103", Int64.Type},
{"21203", Int64.Type},
{"21003", Int64.Type},
{"22103", Int64.Type},
{"22203", Int64.Type},
{"22003", Int64.Type},
{"23103", Int64.Type},
{"23203", Int64.Type},
{"23303", Int64.Type},
{"23403", Int64.Type},
{"23503", Int64.Type},
{"23003", Int64.Type},
{"24103", Int64.Type},
{"24213", Int64.Type},
{"24303", Int64.Type},
{"24503", Int64.Type},
{"24603", Int64.Type},
{"24003", Int64.Type},
{"25103", Int64.Type},
{"25203", Int64.Type},
{"25003", Int64.Type}
}),
#"Дополнительные вычисления 1" = #"Измененный тип 1",
#"Переименованные столбцы 1" = Table.RenameColumns(#"Дополнительные вычисления 1",{{"11003", "11003.1"}}),
#"Переименованные столбцы 2" = Table.RenameColumns(#"Переименованные столбцы 1",{{"12003", "12003.1"}}),
#"Переименованные столбцы 3" = Table.RenameColumns(#"Переименованные столбцы 2",{{"13003", "13003.1"}}),
#"Переименованные столбцы 4" = Table.RenameColumns(#"Переименованные столбцы 3",{{"14003", "14003.1"}}),
#"Переименованные столбцы 5" = Table.RenameColumns(#"Переименованные столбцы 4",{{"15003", "15003.1"}}),
#"Переименованные столбцы 6" = Table.RenameColumns(#"Переименованные столбцы 5",{{"16003", "16003.1"}}),
#"Переименованные столбцы 7" = Table.RenameColumns(#"Переименованные столбцы 6",{{"17003", "17003.1"}}),
#"Добавлен пользовательский объект 1" = Table.AddColumn(#"Переименованные столбцы 7", "11003.2", each [11103]+[11203]+[11303]+[11403]+[11503]+[11603]+[11703]+[11803]+[11903], Int64.Type),
#"Добавлен пользовательский объект 2" = Table.AddColumn(#"Добавлен пользовательский объект 1", "12003.2", each [12103]+[12203]+[12303]+[12403]+[12503]+[12603], Int64.Type),
#"Добавлен пользовательский объект 3" = Table.AddColumn(#"Добавлен пользовательский объект 2", "13003.2", each [13103]+[13203]+[13403]+[13503]+[13603]+[13703], Int64.Type),
#"Добавлен пользовательский объект 4" = Table.AddColumn(#"Добавлен пользовательский объект 3", "14003.2", each [14103]+[14203]+[14303]+[14503], Int64.Type),
#"Добавлен пользовательский объект 5" = Table.AddColumn(#"Добавлен пользовательский объект 4", "15003.2", each [15103]+[15203]+[15303]+[15403]+[15503], Int64.Type),
#"Добавлен пользовательский объект 6" = Table.AddColumn(#"Добавлен пользовательский объект 5", "16003.2", each [11003.2]+[12003.2], Int64.Type),
#"Добавлен пользовательский объект 7" = Table.AddColumn(#"Добавлен пользовательский объект 6", "17003.2", each [13003.2]+[14003.2]+[15003.2], Int64.Type),
#"Промежуточная проверка 1" = Table.AddColumn(#"Добавлен пользовательский объект 7", "Проверка 1", each [13003.2]+[14003.2]+[15003.2]-[11003.2]-[12003.2], Int64.Type),
//#"Удаленные столбцы после преобразования" = Table.RemoveColumns(#"Добавлен пользовательский объект 7",{"11003.1", "12003.1","13003.1","14003.1","15003.1","16003.1","17003.1"}),
//#"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы после преобразования",{"name", "okpo", "okopf", "okfs", "okved", "inn", "measure", "type", "11103", "11203", "11303", "11403", "11503", "11603", "11703", "11803", "11903", "11003", "12103", "12203", "12303", "12403", "12503", "12603", "12003", "16003", "13103", "13203", "13403", "13503", "13603", "13703", "13003", "14103", "14203", "14303", "14503", "14003", "15103", "15203", "15303", "15403", "15503", "15003", "17003", "21103", "21203", "21003", "22103", "22203", "22003", "23103", "23203", "23303", "23403", "23503", "23003", "24103", "24213", "24303", "24503", "24603", "24003", "25103", "25203", "25003", "Year"}),
#"Финальные вычисления 1" = Table.AddColumn(
#"Промежуточная проверка 1",
"11003",
each (
if ([11003.2]<>0) then [11003.2] else ([11003.1])
),
Int64.Type),
#"Переименованные столбцы 1.1" = Table.RenameColumns(#"Финальные вычисления 1",{{"11503", "11503.1"}}),
#"Финальные вычисления 1.1" = Table.AddColumn(
#"Переименованные столбцы 1.1",
"11503",
each (
if ([11003.2]=0 and [11003.1]<>0)
then [11503.1]+([11003.1]-[11003.2])
else [11503.1]
),
Int64.Type),
#"Финальные вычисления 2" = Table.AddColumn(
#"Финальные вычисления 1.1",
"12003",
each (
if ([12003.2]<>0) then [12003.2] else ([12003.1])
),
Int64.Type),
#"Переименованные столбцы 2.1" = Table.RenameColumns(#"Финальные вычисления 2",{{"12303", "12303.1"}}),
#"Финальные вычисления 2.1" = Table.AddColumn(
#"Переименованные столбцы 2.1",
"12303",
each (
if ([12003.2]=0 and [12003.1]<>0)
then [12303.1]+([12003.1]-[12003.2])
else [12303.1]
),
Int64.Type),
#"Финальные вычисления 3" = Table.AddColumn(
#"Финальные вычисления 2.1",
"13003",
each (
if ([13003.2]<>0) then [13003.2] else ([13003.1])
),
Int64.Type),
#"Переименованные столбцы 3.1" = Table.RenameColumns(#"Финальные вычисления 3",{{"13703", "13703.1"}}),
#"Финальные вычисления 3.1" = Table.AddColumn(
#"Переименованные столбцы 3.1",
"13703",
each (
if ([13003.2]=0 and [13003.1]<>0)
then [13703.1]+([13003.1]-[13003.2])
else [13703.1]
),
Int64.Type),
#"Финальные вычисления 4" = Table.AddColumn(
#"Финальные вычисления 3.1",
"14003",
each (
if ([14003.2]<>0) then [14003.2] else ([14003.1])
),
Int64.Type),
#"Переименованные столбцы 4.1" = Table.RenameColumns(#"Финальные вычисления 4",{{"14103", "14103.1"}}),
#"Финальные вычисления 4.1" = Table.AddColumn(
#"Переименованные столбцы 4.1",
"14103",
each (
if ([14003.2]=0 and [14003.1]<>0)
then [14103.1]+([14003.1]-[14003.2])
else [14103.1]
),
Int64.Type),
#"Финальные вычисления 5" = Table.AddColumn(
#"Финальные вычисления 4.1",
"15003",
each (
if ([15003.2]<>0) then [15003.2] else ([15003.1])
),
Int64.Type),
#"Переименованные столбцы 5.1" = Table.RenameColumns(#"Финальные вычисления 5",{{"15203", "15203.1"}}),
#"Финальные вычисления 5.1" = Table.AddColumn(
#"Переименованные столбцы 5.1",
"15203",
each (
if ([15003.2]=0 and [15003.1]<>0)
then [15203.1]+([15003.1]-[15003.2])
else [15203.1]
),
Int64.Type),
#"Промежуточная проверка 2" = Table.AddColumn(#"Финальные вычисления 5.1" , "Проверка 2", each [13003]+[14003]+[15003]-[11003]-[12003], Int64.Type),
#"Вторая итерация корректировки 1" = Table.RemoveColumns(#"Промежуточная проверка 2",
{"13703.1","14103.1","15203.1"}
),
#"Вторая итерация корректировки 2" = Table.RenameColumns(#"Вторая итерация корректировки 1" ,
{
{"13703", "13703.1"},
{"14103", "14103.1"},
{"15203", "15203.1"}
}),
#"Вторая итерация корректировки 3" = Table.AddColumn(
#"Вторая итерация корректировки 2",
"15203",
each (
if (([11003]+[12003])<>([13003]+[14003]+[15003]))
then [15203.1]+Number.RoundDown((([11003]+[12003])-([13003]+[14003]+[15003]))/3)
else [15203.1]
),
Int64.Type),
#"Вторая итерация корректировки 4" = Table.AddColumn(
#"Вторая итерация корректировки 3",
"14103",
each (
if (([11003]+[12003])<>([13003]+[14003]+[15003]))
then [14103.1]+Number.RoundDown((([11003]+[12003])-([13003]+[14003]+[15003]))/3)
else [14103.1]
),
Int64.Type),
#"Вторая итерация корректировки 5" = Table.RemoveColumns(#"Вторая итерация корректировки 4" ,
{"13003.1","14003.1","15003.1"}),
#"Вторая итерация корректировки 6" = Table.RenameColumns(#"Вторая итерация корректировки 5" ,
{
{"13003", "13003.1"},
{"14003", "14003.1"},
{"15003", "15003.1"}
}),
#"Вторая итерация корректировки 7" = Table.AddColumn(#"Вторая итерация корректировки 6",
"14003", each [14103]+[14203]+[14303]+[14503], Int64.Type),
#"Вторая итерация корректировки 8" = Table.AddColumn(#"Вторая итерация корректировки 7" ,
"15003", each [15103]+[15203]+[15303]+[15403]+[15503], Int64.Type),
#"Вторая итерация корректировки 9" = Table.AddColumn(
#"Вторая итерация корректировки 8",
"13703",
each (
if (([11003]+[12003])<>([13003.1]+[14003]+[15003]))
then [13703.1]+([11003]+[12003])-([13003.1]+[14003]+[15003])
else [13703.1]
),
Int64.Type),
#"Вторая итерация корректировки 10" = Table.AddColumn(#"Вторая итерация корректировки 9",
"13003", each [13103]+[13203]+[13403]+[13503]+[13603]+[13703], Int64.Type),
#"Финальные вычисления 6.1" = Table.AddColumn(
#"Вторая итерация корректировки 10",
"16003",
each (
[11003]+[12003]
),
Int64.Type),
#"Финальные вычисления 7.1" = Table.AddColumn(
#"Финальные вычисления 6.1" ,
"17003",
each (
[13003]+[14003]+[15003]
),
Int64.Type),
#"Промежуточная проверка 3" = Table.AddColumn(#"Финальные вычисления 7.1" , "Проверка 3", each [13003]+[14003]+[15003]-[11003]-[12003], Int64.Type),
#"Финальные вычисления" = Table.RemoveColumns(#"Промежуточная проверка 3",
{ "11003.1", "11003.2", "11503.1",
"12003.1", "12003.2", "12303.1",
"13003.1", "13003.2", "13703.1",
"14003.1", "14003.2", "14103.1",
"15003.1", "15003.2", "15203.1",
"16003.1", "16003.2",
"17003.1", "17003.2",
"Проверка 1", "Проверка 2", "Проверка 3"
}
),
#"Измененный тип 3" =
Table.TransformColumnTypes(#"Финальные вычисления",{
{"11503", Int64.Type},
{"12303", Int64.Type},
{"13703", Int64.Type},
{"14103", Int64.Type},
{"15203", Int64.Type},
{"11003", Int64.Type},
{"12003", Int64.Type},
{"16003", Int64.Type},
{"13003", Int64.Type},
{"14003", Int64.Type},
{"15003", Int64.Type},
{"17003", Int64.Type}
}),
#"Переупорядоченные столбцы"= Table.ReorderColumns(#"Финальные вычисления",{"name", "okpo", "okopf", "okfs", "okved", "inn", "measure", "type", "11103", "11203", "11303", "11403", "11503", "11603", "11703", "11803", "11903", "11003", "12103", "12203", "12303", "12403", "12503", "12603", "12003", "16003", "13103", "13203", "13403", "13503", "13603", "13703", "13003", "14103", "14203", "14303", "14503", "14003", "15103", "15203", "15303", "15403", "15503", "15003", "17003" , "21103", "21203", "21003", "22103", "22203", "22003", "23103", "23203", "23303", "23403", "23503", "23003", "24103", "24213", "24303", "24503", "24603", "24003", "25103", "25203", "25003", "Year"}),
#"Результат" = #"Переупорядоченные столбцы"
in
Результат2012
let
#"Год" = 2012,
#"Путь расположения файлов" = #"FilesDirectory",
#"Наименование файла данных" = Lines.ToText(
{
"data-20200331-structure-",
Number.ToText(#"Год"),
"1231"
},
"") as text,
#"Наименование файла структуры" = Lines.ToText(
{
"structure-",
Number.ToText(#"Год"),
"1231"
},
"") as text,
#"Расширение файла" = "csv",
#"Длина таблица данных" = 124,
#"Импорт_1" = Lines.ToText(
{
#"Путь расположения файлов",
#"Наименование файла данных",
".",
#"Расширение файла"
},
"") as text,
#"Данные_1" = Csv.Document(File.Contents(#"Импорт_1"),
[Delimiter=";",
Columns=#"Длина таблица данных",
Encoding=1251,
QuoteStyle=QuoteStyle.None]),
#"Импорт_2" = Lines.ToText(
{
#"Путь расположения файлов",
"Help",
".",
#"Расширение файла"
},
"") as text,
#"Расшифровка_1" = Csv.Document(File.Contents(#"Импорт_2"),
[Delimiter=";",
Columns=3,
Encoding=1251,
QuoteStyle=QuoteStyle.None]),
#"Расшифровка_2" = Table.RenameColumns(#"Расшифровка_1",{{"Column1", "Numer"}, {"Column2", "Name_RUS"}, {"Column3", "Name_ENG"}}),
#"Импорт_3" = Lines.ToText(
{
#"Путь расположения файлов",
#"Наименование файла структуры",
".",
#"Расширение файла"
},
"") as text,
#"Структура_1" = Csv.Document(File.Contents(#"Импорт_3"),
[Delimiter=",",
Columns=4,
Encoding=1251,
QuoteStyle=QuoteStyle.Csv]),
#"Структура_2" = Table.PromoteHeaders (#"Структура_1"),
#"Список_полей_1" = #"Структура_2"[#"field name"],
#"Список_полей_2"=List.FirstN(#"Список_полей_1", #"Длина таблица данных"),
#"Список_полей_3" = Table.ColumnNames(#"Данные_1"),
#"Список_полей_4" = List.Zip({#"Список_полей_3",#"Список_полей_2"}),
#"Данные_2" = Table.RenameColumns(#"Данные_1", #"Список_полей_4"),
#"Перечень стобцов" = Table.FromList(Table.ColumnNames(#"Данные_2")),
#"Перечень стобцов с фильтром" = Table.SelectRows(#"Перечень стобцов", each
(Text.Length([Column1]) =5) and (Text.At([Column1], 4)="4")),
#"Перечень стобцов с фильтром в виде списка" = #"Перечень стобцов с фильтром"[Column1],
#"Удаленные столбцы новый" = Table.RemoveColumns(#"Данные_2",#"Перечень стобцов с фильтром в виде списка", MissingField.Ignore),
#"Вид таблицы с добавленным годом" = Table.AddColumn(#"Удаленные столбцы новый" , "Year", each #"Год"),
#"Финальный вид таблицы" = Table.TransformColumns(
#"Вид таблицы с добавленным годом",
{
{
"okved",
each
Text.Insert(
(_),0,
(if #"Год" <= 2016
then "ОКВЭД: "
else "ОКВЭД2: ")
),
type text
}
}
),
//Блок конвертации по кода 383, 384 ...
lst1 = List.Skip(Table.ColumnNames(#"Финальный вид таблицы" ), 8),
lst=List.Range(lst1,0,58),
f=(x,y)=>List.Accumulate(lst,x,(s,c)=>Record.TransformFields(s,{c,(x)=>Number.From(x)*y})),
g=(x)=> f(x,Number.Power(10, (Number.FromText(x[#"measure"])-383)*3)),
tbl = Table.ToRecords(#"Финальный вид таблицы"),
trnsf= List.Transform(tbl,g),
#"Окончательный вид таблицы перед дополнительными вычислениями" = Table.FromRecords(trnsf),
//...
#"Измененный тип 1" =
Table.TransformColumnTypes(#"Окончательный вид таблицы перед дополнительными вычислениями",{
{"11103", Int64.Type},
{"11203", Int64.Type},
{"11303", Int64.Type},
{"11403", Int64.Type},
{"11503", Int64.Type},
{"11603", Int64.Type},
{"11703", Int64.Type},
{"11803", Int64.Type},
{"11903", Int64.Type},
{"11003", Int64.Type},
{"12103", Int64.Type},
{"12203", Int64.Type},
{"12303", Int64.Type},
{"12403", Int64.Type},
{"12503", Int64.Type},
{"12603", Int64.Type},
{"12003", Int64.Type},
{"16003", Int64.Type},
{"13103", Int64.Type},
{"13203", Int64.Type},
{"13403", Int64.Type},
{"13503", Int64.Type},
{"13603", Int64.Type},
{"13703", Int64.Type},
{"13003", Int64.Type},
{"14103", Int64.Type},
{"14203", Int64.Type},
{"14303", Int64.Type},
{"14503", Int64.Type},
{"14003", Int64.Type},
{"15103", Int64.Type},
{"15203", Int64.Type},
{"15303", Int64.Type},
{"15403", Int64.Type},
{"15503", Int64.Type},
{"15003", Int64.Type},
{"17003", Int64.Type},
{"21103", Int64.Type},
{"21203", Int64.Type},
{"21003", Int64.Type},
{"22103", Int64.Type},
{"22203", Int64.Type},
{"22003", Int64.Type},
{"23103", Int64.Type},
{"23203", Int64.Type},
{"23303", Int64.Type},
{"23403", Int64.Type},
{"23503", Int64.Type},
{"23003", Int64.Type},
{"24103", Int64.Type},
{"24213", Int64.Type},
{"24303", Int64.Type},
{"24503", Int64.Type},
{"24603", Int64.Type},
{"24003", Int64.Type},
{"25103", Int64.Type},
{"25203", Int64.Type},
{"25003", Int64.Type}
}),
#"Дополнительные вычисления 1" = #"Измененный тип 1",
#"Переименованные столбцы 1" = Table.RenameColumns(#"Дополнительные вычисления 1",{{"11003", "11003.1"}}),
#"Переименованные столбцы 2" = Table.RenameColumns(#"Переименованные столбцы 1",{{"12003", "12003.1"}}),
#"Переименованные столбцы 3" = Table.RenameColumns(#"Переименованные столбцы 2",{{"13003", "13003.1"}}),
#"Переименованные столбцы 4" = Table.RenameColumns(#"Переименованные столбцы 3",{{"14003", "14003.1"}}),
#"Переименованные столбцы 5" = Table.RenameColumns(#"Переименованные столбцы 4",{{"15003", "15003.1"}}),
#"Переименованные столбцы 6" = Table.RenameColumns(#"Переименованные столбцы 5",{{"16003", "16003.1"}}),
#"Переименованные столбцы 7" = Table.RenameColumns(#"Переименованные столбцы 6",{{"17003", "17003.1"}}),
#"Добавлен пользовательский объект 1" = Table.AddColumn(#"Переименованные столбцы 7", "11003.2", each [11103]+[11203]+[11303]+[11403]+[11503]+[11603]+[11703]+[11803]+[11903], Int64.Type),
#"Добавлен пользовательский объект 2" = Table.AddColumn(#"Добавлен пользовательский объект 1", "12003.2", each [12103]+[12203]+[12303]+[12403]+[12503]+[12603], Int64.Type),
#"Добавлен пользовательский объект 3" = Table.AddColumn(#"Добавлен пользовательский объект 2", "13003.2", each [13103]+[13203]+[13403]+[13503]+[13603]+[13703], Int64.Type),
#"Добавлен пользовательский объект 4" = Table.AddColumn(#"Добавлен пользовательский объект 3", "14003.2", each [14103]+[14203]+[14303]+[14503], Int64.Type),
#"Добавлен пользовательский объект 5" = Table.AddColumn(#"Добавлен пользовательский объект 4", "15003.2", each [15103]+[15203]+[15303]+[15403]+[15503], Int64.Type),
#"Добавлен пользовательский объект 6" = Table.AddColumn(#"Добавлен пользовательский объект 5", "16003.2", each [11003.2]+[12003.2], Int64.Type),
#"Добавлен пользовательский объект 7" = Table.AddColumn(#"Добавлен пользовательский объект 6", "17003.2", each [13003.2]+[14003.2]+[15003.2], Int64.Type),
#"Промежуточная проверка 1" = Table.AddColumn(#"Добавлен пользовательский объект 7", "Проверка 1", each [13003.2]+[14003.2]+[15003.2]-[11003.2]-[12003.2], Int64.Type),
//#"Удаленные столбцы после преобразования" = Table.RemoveColumns(#"Добавлен пользовательский объект 7",{"11003.1", "12003.1","13003.1","14003.1","15003.1","16003.1","17003.1"}),
//#"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы после преобразования",{"name", "okpo", "okopf", "okfs", "okved", "inn", "measure", "type", "11103", "11203", "11303", "11403", "11503", "11603", "11703", "11803", "11903", "11003", "12103", "12203", "12303", "12403", "12503", "12603", "12003", "16003", "13103", "13203", "13403", "13503", "13603", "13703", "13003", "14103", "14203", "14303", "14503", "14003", "15103", "15203", "15303", "15403", "15503", "15003", "17003", "21103", "21203", "21003", "22103", "22203", "22003", "23103", "23203", "23303", "23403", "23503", "23003", "24103", "24213", "24303", "24503", "24603", "24003", "25103", "25203", "25003", "Year"}),
#"Финальные вычисления 1" = Table.AddColumn(
#"Промежуточная проверка 1",
"11003",
each (
if ([11003.2]<>0) then [11003.2] else ([11003.1])
),
Int64.Type),
#"Переименованные столбцы 1.1" = Table.RenameColumns(#"Финальные вычисления 1",{{"11503", "11503.1"}}),
#"Финальные вычисления 1.1" = Table.AddColumn(
#"Переименованные столбцы 1.1",
"11503",
each (
if ([11003.2]=0 and [11003.1]<>0)
then [11503.1]+([11003.1]-[11003.2])
else [11503.1]
),
Int64.Type),
#"Финальные вычисления 2" = Table.AddColumn(
#"Финальные вычисления 1.1",
"12003",
each (
if ([12003.2]<>0) then [12003.2] else ([12003.1])
),
Int64.Type),
#"Переименованные столбцы 2.1" = Table.RenameColumns(#"Финальные вычисления 2",{{"12303", "12303.1"}}),
#"Финальные вычисления 2.1" = Table.AddColumn(
#"Переименованные столбцы 2.1",
"12303",
each (
if ([12003.2]=0 and [12003.1]<>0)
then [12303.1]+([12003.1]-[12003.2])
else [12303.1]
),
Int64.Type),
#"Финальные вычисления 3" = Table.AddColumn(
#"Финальные вычисления 2.1",
"13003",
each (
if ([13003.2]<>0) then [13003.2] else ([13003.1])
),
Int64.Type),
#"Переименованные столбцы 3.1" = Table.RenameColumns(#"Финальные вычисления 3",{{"13703", "13703.1"}}),
#"Финальные вычисления 3.1" = Table.AddColumn(
#"Переименованные столбцы 3.1",
"13703",
each (
if ([13003.2]=0 and [13003.1]<>0)
then [13703.1]+([13003.1]-[13003.2])
else [13703.1]
),
Int64.Type),
#"Финальные вычисления 4" = Table.AddColumn(
#"Финальные вычисления 3.1",
"14003",
each (
if ([14003.2]<>0) then [14003.2] else ([14003.1])
),
Int64.Type),
#"Переименованные столбцы 4.1" = Table.RenameColumns(#"Финальные вычисления 4",{{"14103", "14103.1"}}),
#"Финальные вычисления 4.1" = Table.AddColumn(
#"Переименованные столбцы 4.1",
"14103",
each (
if ([14003.2]=0 and [14003.1]<>0)
then [14103.1]+([14003.1]-[14003.2])
else [14103.1]
),
Int64.Type),
#"Финальные вычисления 5" = Table.AddColumn(
#"Финальные вычисления 4.1",
"15003",
each (
if ([15003.2]<>0) then [15003.2] else ([15003.1])
),
Int64.Type),
#"Переименованные столбцы 5.1" = Table.RenameColumns(#"Финальные вычисления 5",{{"15203", "15203.1"}}),
#"Финальные вычисления 5.1" = Table.AddColumn(
#"Переименованные столбцы 5.1",
"15203",
each (
if ([15003.2]=0 and [15003.1]<>0)
then [15203.1]+([15003.1]-[15003.2])
else [15203.1]
),
Int64.Type),
#"Промежуточная проверка 2" = Table.AddColumn(#"Финальные вычисления 5.1" , "Проверка 2", each [13003]+[14003]+[15003]-[11003]-[12003], Int64.Type),
#"Вторая итерация корректировки 1" = Table.RemoveColumns(#"Промежуточная проверка 2",
{"13703.1","14103.1","15203.1"}
),
#"Вторая итерация корректировки 2" = Table.RenameColumns(#"Вторая итерация корректировки 1" ,
{
{"13703", "13703.1"},
{"14103", "14103.1"},
{"15203", "15203.1"}
}),
#"Вторая итерация корректировки 3" = Table.AddColumn(
#"Вторая итерация корректировки 2",
"15203",
each (
if (([11003]+[12003])<>([13003]+[14003]+[15003]))
then [15203.1]+Number.RoundDown((([11003]+[12003])-([13003]+[14003]+[15003]))/3)
else [15203.1]
),
Int64.Type),
#"Вторая итерация корректировки 4" = Table.AddColumn(
#"Вторая итерация корректировки 3",
"14103",
each (
if (([11003]+[12003])<>([13003]+[14003]+[15003]))
then [14103.1]+Number.RoundDown((([11003]+[12003])-([13003]+[14003]+[15003]))/3)
else [14103.1]
),
Int64.Type),
#"Вторая итерация корректировки 5" = Table.RemoveColumns(#"Вторая итерация корректировки 4" ,
{"13003.1","14003.1","15003.1"}),
#"Вторая итерация корректировки 6" = Table.RenameColumns(#"Вторая итерация корректировки 5" ,
{
{"13003", "13003.1"},
{"14003", "14003.1"},
{"15003", "15003.1"}
}),
#"Вторая итерация корректировки 7" = Table.AddColumn(#"Вторая итерация корректировки 6",
"14003", each [14103]+[14203]+[14303]+[14503], Int64.Type),
#"Вторая итерация корректировки 8" = Table.AddColumn(#"Вторая итерация корректировки 7" ,
"15003", each [15103]+[15203]+[15303]+[15403]+[15503], Int64.Type),
#"Вторая итерация корректировки 9" = Table.AddColumn(
#"Вторая итерация корректировки 8",
"13703",
each (
if (([11003]+[12003])<>([13003.1]+[14003]+[15003]))
then [13703.1]+([11003]+[12003])-([13003.1]+[14003]+[15003])
else [13703.1]
),
Int64.Type),
#"Вторая итерация корректировки 10" = Table.AddColumn(#"Вторая итерация корректировки 9",
"13003", each [13103]+[13203]+[13403]+[13503]+[13603]+[13703], Int64.Type),
#"Финальные вычисления 6.1" = Table.AddColumn(
#"Вторая итерация корректировки 10",
"16003",
each (
[11003]+[12003]
),
Int64.Type),
#"Финальные вычисления 7.1" = Table.AddColumn(
#"Финальные вычисления 6.1" ,
"17003",
each (
[13003]+[14003]+[15003]
),
Int64.Type),
#"Промежуточная проверка 3" = Table.AddColumn(#"Финальные вычисления 7.1" , "Проверка 3", each [13003]+[14003]+[15003]-[11003]-[12003], Int64.Type),
#"Финальные вычисления" = Table.RemoveColumns(#"Промежуточная проверка 3",
{ "11003.1", "11003.2", "11503.1",
"12003.1", "12003.2", "12303.1",
"13003.1", "13003.2", "13703.1",
"14003.1", "14003.2", "14103.1",
"15003.1", "15003.2", "15203.1",
"16003.1", "16003.2",
"17003.1", "17003.2",
"Проверка 1", "Проверка 2", "Проверка 3"
}
),
#"Измененный тип 3" =
Table.TransformColumnTypes(#"Финальные вычисления",{
{"11503", Int64.Type},
{"12303", Int64.Type},
{"13703", Int64.Type},
{"14103", Int64.Type},
{"15203", Int64.Type},
{"11003", Int64.Type},
{"12003", Int64.Type},
{"16003", Int64.Type},
{"13003", Int64.Type},
{"14003", Int64.Type},
{"15003", Int64.Type},
{"17003", Int64.Type}
}),
#"Переупорядоченные столбцы"= Table.ReorderColumns(#"Финальные вычисления",{"name", "okpo", "okopf", "okfs", "okved", "inn", "measure", "type", "11103", "11203", "11303", "11403", "11503", "11603", "11703", "11803", "11903", "11003", "12103", "12203", "12303", "12403", "12503", "12603", "12003", "16003", "13103", "13203", "13403", "13503", "13603", "13703", "13003", "14103", "14203", "14303", "14503", "14003", "15103", "15203", "15303", "15403", "15503", "15003", "17003" , "21103", "21203", "21003", "22103", "22203", "22003", "23103", "23203", "23303", "23403", "23503", "23003", "24103", "24213", "24303", "24503", "24603", "24003", "25103", "25203", "25003", "Year"}),
#"Результат" = #"Переупорядоченные столбцы"
in
#"Результат"Если скачанные файлы вами не переименовывались и лежат в одном месте, а также вы указали папку в FilesDirectory, то повторив запрос 2012 для каждого из файлов, мы их все интегрируем и предварительно обработаем, чего, собственно, и добивались.
Однако работать с такими данными не представляется возможным, потому их потребуется переработать. Для этих целей создаётся запрос Fact:
Fact
let
#"Исходные данные" = Table.Combine({#"2018", #"2017", #"2016", #"2015", #"2014", #"2013", #"2012", #"2011"}),
//#"Исходные данные" = Table.Combine({#"2012"}),
#"Развёрнутая таблица" = Table.UnpivotOtherColumns(#"Исходные данные",
List.Combine({List.FirstN(Table.ColumnNames( #"Исходные данные"),8),{"Year"}}), "Attribute", "Value"),
#"Список колонок на удаление" = List.RemoveItems(Table.ColumnNames(#"Развёрнутая таблица"),{"inn", "Year", "okved", "Attribute", "Value"}),
#"Таблица с удалёнными колонками" = Table.RemoveColumns(#"Развёрнутая таблица",#"Список колонок на удаление" ),
#"Таблица с корректным типами" = Table.TransformColumnTypes(#"Таблица с удалёнными колонками",{{"inn", Text.Type}, {"okved", Text.Type}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
#"Переупорядоченные столбцы" = Table.ReorderColumns(#"Таблица с корректным типами",{"inn", "okved", "Year", "Attribute", "Value"}),
//#"Строки с примененным фильтром" = Table.SelectRows(Результат, each true)
#"Результат" = #"Переупорядоченные столбцы"
in
РезультатСобственно, данный запрос формирует то, что нам нужно - таблица приемлемого формата, по которой теперь можно "фильтроваться", считать суммы и т.д. Эта таблица соответствует и принципам нормальности из СУБД.
Если вы, кстати, внимательно посмотрите на все запросы, то увидите, что помимо файлов с данными потребуется некоторое количество дополнительных файлов: файлы структуры для каждой из баз, файл help (там расшифровки и названия для колонок таблиц с данными).
Для файла Help также формируется отдельный запрос:
Help
let
#"Путь расположения файлов" = #"FilesDirectory",
#"Расширение файла" = "csv",
#"Импорт_2" = Lines.ToText(
{
#"Путь расположения файлов",
"Help",
".",
#"Расширение файла"
},
"") as text,
#"Расшифровка_1" = Csv.Document(File.Contents(#"Импорт_2"),
[Delimiter=";",
Columns=3,
Encoding=1251,
QuoteStyle=QuoteStyle.None]),
#"Расшифровка_2" = Table.RenameColumns(#"Расшифровка_1",{{"Column1", "Numer"}, {"Column2", "Name_RUS"}, {"Column3", "Name_ENG"}}),
#"Результат"= #"Расшифровка_2",
#"Измененный тип" = Table.TransformColumnTypes(Результат,{{"Numer", type text}})
in
#"Измененный тип"Далее потребуется выгрузить элементы фильтрации, т.е. ОКВЭД, год, Регион. Выглядит это так:
Чтобы можно было выделить отдельное предприятие и сразу просмотреть его анализ, выделяем весь имеющийся перечень:
Enterprises
let #"Source" = Table.Combine({#"2018", #"2017", #"2016", #"2015", #"2014", #"2013", #"2012", #"2011"}), #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Source",{"name", "okpo", "okopf", "okfs", "okved", "inn", "measure", "type", "11103", "11203", "11303", "11403", "11503", "11603", "11703", "11803", "11903", "11003", "12103", "12203", "12303", "12403", "12503", "12603", "12003", "16003", "13103", "13203", "13403", "13503", "13603", "13703", "13003", "14103", "14203", "14303", "14503", "14003", "15103", "15203", "15303", "15403", "15503", "15003", "17003", "21103", "21203", "21003", "22103", "22203", "22003", "23103", "23203", "23303", "23403", "23503", "23003", "24103", "24213", "24303", "24503", "24603", "24003", "25103", "25203", "25003", "Year"}), #"Removed Other Columns" = Table.SelectColumns(Source,List.FirstN(Table.ColumnNames(#"Переупорядоченные столбцы"),8)), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", "inn") in #"Removed Duplicates"Могут понадобиться коды всех используемых строк финансовой отчётности:
Codes
let Source = Fact, #"Removed Other Columns" = Table.SelectColumns(Source,{"Attribute"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns") in #"Removed Duplicates"Вытащим отдельно годы:
Years
let Source = Fact, #"Removed Other Columns" = Table.SelectColumns(Source,{"Year"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns") in #"Removed Duplicates"Сформируем перечень регионов:
Reg
let Источник = #table( type table [ID = text, GegName = text], { {"01","Адыгея республика"}, {"02","Башкортостан республика"}, {"03","Бурятия республика"}, {"04","Алтай республика"}, {"05","Дагестан республика"}, {"06","Ингушетия республика"}, {"07","Кабардино-Балкарская республика"}, {"08","Калмыкия республика"}, {"09","Карачаево-Черкесская республика"}, {"10","Карелия республика"}, {"11","Коми республика"}, {"12","Марий Эл республика"}, {"13","Мордовия республика"}, {"14","Саха /Якутия/ республика"}, {"15","Северная Осетия - Алания республика"}, {"16","Татарстан республика"}, {"17","Тыва республика"}, {"18","Удмуртская республика"}, {"19","Хакасия республика"}, {"20","Чеченская республика"}, {"21","Чувашская Республика - Чувашия"}, {"22","Алтайский край"}, {"23","Краснодарский край"}, {"24","Красноярский край"}, {"25","Приморский край"}, {"26","Ставропольский край"}, {"27","Хабаровский край"}, {"28","Амурская область"}, {"29","Архангельская область"}, {"30","Астраханская область"}, {"31","Белгородская область"}, {"32","Брянская область"}, {"33","Владимирская область"}, {"34","Волгоградская область"}, {"35","Вологодская область"}, {"36","Воронежская область"}, {"37","Ивановская область"}, {"38","Иркутская область"}, {"39","Калининградская область"}, {"40","Калужская область"}, {"41","Камчатский край"}, {"42","Кемеровская область"}, {"43","Кировская область"}, {"44","Костромская область"}, {"45","Курганская область"}, {"46","Курская область"}, {"47","Ленинградская область"}, {"48","Липецкая область"}, {"49","Магаданская область"}, {"50","Московская область"}, {"51","Мурманская область"}, {"52","Нижегородская область"}, {"53","Новгородская область"}, {"54","Новосибирская область"}, {"55","Омская область"}, {"56","Оренбургская область"}, {"57","Орловская область"}, {"58","Пензенская область"}, {"59","Пермский край"}, {"60","Псковская область"}, {"61","Ростовская область"}, {"62","Рязанская область"}, {"63","Самарская область"}, {"64","Саратовская область"}, {"65","Сахалинская область"}, {"66","Свердловская область"}, {"67","Смоленская область"}, {"68","Тамбовская область"}, {"69","Тверская область"}, {"70","Томская область"}, {"71","Тульская область"}, {"72","Тюменская область"}, {"73","Ульяновская область"}, {"74","Челябинская область"}, {"75","Забайкальский край"}, {"76","Ярославская область"}, {"77","Москва город"}, {"78","Санкт-Петербург город"}, {"79","Еврейская автономная область"}, {"83","Ненецкий автономный округ"}, {"86","Ханты-Мансийский Автономный округ - Югра автономный округ"}, {"87","Чукотский автономный округ"}, {"89","Ямало-Ненецкий автономный округ"}, {"91","Крым республика"}, {"92","Севастополь город"}, {"99","Байконур город"} } ), #"Переименованные столбцы" = Table.RenameColumns(Источник,{{"GegName", "Регион"}}) in #"Переименованные столбцы"Выделим перечень ОКВЭД-ов:
okved
let Source = Fact, #"Removed Other Columns" = Table.SelectColumns(Source,{"okved"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns") in #"Removed Duplicates"
Собственно, на этом временно с запросами можно закончить и выгрузить все, нажав на кнопку "Закрыть и применить". Перечень запросов к этой стадии реализации выглядит указанным образом.
Не обольщайтесь, проделана лишь часть работы, но она одна из ключевых.
После начала загрузки у меня перерасчёт и дозагрузка происходит примерно ~ 24 часа. Не могу сказать норма ли это. При этом устройство, на котором выполнялась работа - достаточно мощное.
"Заблюренные" таблицы у Вас появятся чуть позже, а сейчас следует, согласно нашего плана, структурировать данные и выстроить взаимосвязи.
После "импорта" данных потребуется их структурировать, указав взаимосвязи между таблицами. Это ключевой и необходимый момент, чтобы появилась возможность фильтрации и выбора данных по признакам. Общий вид таков:
Практически все связи выстраивались в логике 1 ко многим. Power BI предоставляет посмотреть структуру связей в формате таблицы:
По итогу этих действий мы полностью готовы к формированию отчёта и разработке представлений.
Разработка интерфейса (плюшки, особенности)
Итоговый внешний вид пользовательского интерфейса получился таковым:
Слева пользователю доступны "разделы" (на самом деле, страницы) отчёта, перемещение по которым производится нажатием левой кнопки мыши (ЛКМ) при зажатой клавише ctrl.
Интересным аспектом данной страницы является ограничение "бегунком" (модифицированным срезом) числа, демонстрируемых объектов в диаграмме и таблице.
Как реализовано? А вот так...
Сначала создаётся простая одностолбцовая таблица:
Кол-во в рейтинге = GENERATESERIES(5, 88, 1)Кидаем срез на лист, указываем его тип :"одиночное значение", в качестве поля (аргумента), указываем "Кол-во в рейтинге". Теперь срез будет принимать только указанные в данной таблице значения. Следующая функция будет указывать, какое именно значение выбрано фильтром:
Значение Кол-ва в рейтинге = SELECTEDVALUE('Кол-во в рейтинге'[Кол-во в рейтинге])Теперь необходимо по каждому региону выставить рейтинг в зависимости от числа предприятий в нём:
РангРегионаПоКоличествуПредприятий =
VAR REGToRank = [Значение Кол-ва в рейтинге]
VAR Ranking =
RANKX(
ALLSELECTED(NewReg[Регион]), COUNTROWS ( RELATEDTABLE (Enterprises))
)
VAR IsOtherSelected = SELECTEDVALUE(NewReg[Регион]) = "Другие"
VAR Result = IF (IsOtherSelected, REGToRank+1,
IF (Ranking<=REGToRank,Ranking,Ranking+1))
RETURN ResultЧто осталось сделать... указать для регионов, чей рейтинг в пределе интересующего их соответствие, а остальным - нет:
Кол-воВРейтинге =
VAR Ranking = [РангРегионаПоКоличествуПредприятий]
VAR TopNValue = [Значение Кол-ва в рейтинге]
VAR Result = Int(Ranking<=TopNValue+1)
RETURN
ResultВ итоге, настроив для графического элемента фильтр, можно показывать только те регионы, что попали в интересующий рейтинг:
Поскольку суммарные значения по денежным позициям весьма велики, то для управления отображением значений пришлось реализовать переключатель единиц измерения. Например, на втором отчёте он уже появляется:
Реализация переключения единиц измерения
В первую очередь нужно прямо в Power BI создать таблицу с применением синтаксиса DAX (для разнообразия, так сказать, а то всё PQ, да PQ):
Display = DATATABLE("Type",STRING,
{
{"1. Рубли"},
{"2. Тысячи рублей"},
{"3. Миллионы рублей"},
{"4. Миллиарды рублей"},
{"5. Триллионы рублей"},
{"6. Квадриллионы рублей"}
}
)Теперь можно создать срез и сразу на него и разместить Type.
Ну а дальше всё достаточно просто. В любой элемент размещаются не сами значения, а их "форматированные копии". А для этих самых копий пишется такой синтаксис:
Formatted Value =
SWITCH(
TRUE(),
SELECTEDVALUE(Display[Type]) = "1. Рубли", FORMAT(SUM(Fact[Value]), "### ### ### ### ##0"),
SELECTEDVALUE(Display[Type]) = "2. Тысячи рублей", FORMAT(SUM(Fact[Value])/1000, "### ### ### ### ##0"),
SELECTEDVALUE(Display[Type]) = "3. Миллионы рублей", FORMAT(SUM(Fact[Value])/1000000, "### ### ### ### ##0"),
SELECTEDVALUE(Display[Type]) = "4. Миллиарды рублей", FORMAT(SUM(Fact[Value])/1000000000, "### ### ### ### ##0"),
SELECTEDVALUE(Display[Type]) = "5. Триллионы рублей", FORMAT(SUM(Fact[Value])/1000000000000, "### ### ### ### ##0"),
SELECTEDVALUE(Display[Type]) = "6. Квадриллионы рублей", FORMAT(SUM(Fact[Value])/1000000000000000, "### ### ### ### ##0")
)Ну вот, более-менее и прояснилось всё.
В отчёте содержится анализ по форме 2:
Включил в отчёт анализ по основным финансовым коэффициентам:
Отдельное внимание стоило уделить анализу ликвидности, поэтому в отчёте ему посвящается отдельная страница:
Исключительного "описательного" внимания заслуживает реализация модели Дюпон.
Иной способ отобразить всю модель и по всем периодам не пришёл на ум. Здесь требуется перемещаться по чёрным стрелкам. Внизу для модели указаны терминальные критерии, т.е. те, на основе которых выстраиваются все дальнейшие вычисления. Данные критерии принимаются или вычисляются по формам отчётности. Каждому такому критерию указан графический символ (кругляшом определённого цвета). Поднимаясь на уровень выше с критериями проводятся математические действия и результат также получает свой графический символ.
Что можно ещё добавить именно по разработке модели? Не приходит на ум.
Как исследовать и работать с моделью
Модель призвана для решения следующих задач (это не исчерпывающий список, а то, что пришло в голову):
Сравнить тенденции регионов с общей тенденцией в России и между собой;
Сравнить тенденции отдельных отраслей (через выбор ОКВЭД);
Сравнить тенденцию отдельного предприятия с общими по региону, ОКВЭД-ам и России в целом;
Определить применимость рекомендованных значений коэффициентов, моделей и т.д. для отдельных видов предприятий, отдельных субъектов;
Выявить "аномалии" (странности) в динамики и попытаться определить основные причины этих событий.
Наверно и всё.
Заключение
Ещё раз позвольте выразить убеждённость, что данный труд, модель и статья не "лягут на полку", а окажутся действительно полезными для вас! Если у Вас найдётся возможность помочь моему труду, предоставив актуальные базы за 2019,2020 годы, то буду крайне благодарен. Если сообщество решит меня поддержать материально, то уверяю, что если средств хватит - потрачу их на приобретение баз.
Отдельно обращаюсь к службам статистики. Товарищи! Проверьте, пожалуйста, базы данных и корректность их ведения! В них есть и достаточно много ошибок. На основе этих данных проводится экономический анализ и принимаются решения в части финансирования и управления.
Отдельное обращение к представителям финансовых организаций. Пожалуйста, не пользуйтесь слепо аналитическими моделями. Ситуация меняется, модели подходят не всем видам бизнеса, сами модели устаревают.
Рад был представить Вам эту работу.
P.S. Данные и результаты работы можно совершенно бесплатно получить на моём канале в Telegramm.
P.S.2. @Microsoft! Являюсь не просто Вашим поклонником, но и адептом! Позвольте порекомендовать Вам расширить возможность бесплатной публикации отчётов для некоммерческих и учебных проектов. С одной стороны, Вы повысите популярность собственного инструмента, с другой стороны, сделаете мир немного лучше. Я хотел опубликовать этот отчёт онлайн, но возможности Power BI для бесплатной публикации ограничиваются 1 Гб размещаемой информации.
