translate_metrika

let
    raw_data = #"raw_metrika", // замените на название вашей выгрузки из сырой метрики
    
    column_names = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVvdbxvHEf9XDnpqACeAv17yZlO248KyVYtygQZ+OJFL8iLqjtg7StabKEWuU7lVaxtooCax0xZtgT6ElkRL1gcF+C+4+486M/e1e7f3RREF4liiZ34zOzs7Mzuz/PrrmfWVL+0vVw3bcO7PzlyZMfsrS4zDD+737qE7cs+9TffY+9Y9dk/cIfw89jY0dx9+PYKPN93hzNMrAUbD6psO4wmUH92xe+aOgMsbeL+HHzbh/wQWc67pTqNzf9YGeoc9c5DtrbcBogbAOgaODW9HA4FDQXLM3dQdBizBX+4bUnOYoSRS1Y2VkMHxf4yZjoEPRI/cM2+3AGKxXhNXumgazzTEsx19pReTG/ZDtrZoE01slrdoEhC1434UxGjuBa16REbfBDVOvd0YCpC5s/j4gWCnn9E67tA9B/rntGpvGyx+KCrMzGYh006arae32RODrdmS4q9BpS33PWh8jpwX8j6J/ORTs32uO4ZlShivYgsjCLAP3Y+43Jh5CZypwSSun4AC3AYsdY4iQdxLwcq9W80mZ7bgQ/fnNaA+RFneIKbkrA361NBZ+brSKMMUseGIlK/xEKC1MkDlE3B/ViNlCdvbUWGnGNyDUIS8IVxfERZ47079c3CYIUGf0dGig6I+nV2DmU5aEjgc7OhLYBgD4zDtdCZz1iy+XF/vMcEGfwcZF7hzY6A/JG8YkT8JnG1L79okMdwRMMVzxIc/HxN0s/HBDGUIbnLo7zjI+RDKyYaa50bCdf5FTJHbhKzHCcZHvEn0oQI5ERCCUeCMYLYETK3POTMb6+WQwOhg8D+BXoJvdHXbqXO91TIaC1afN0SzfE+WGEMYPUckzfetEFbGuNVcvWO2DVPkfweanMDq0WfA2dGSA1BrQFt/lkR4zFqM692UFn4AG/lhAxDiUywZBCEWmM4bHV+Px5blSChjFA36+N6X1EX7lXtCq8OjP/4sG7cCZgLEahh696Hv5DFKDQz8HCw6pMMRmWkkR50UwDy3WkZX1OY1nMktOGUXfqCTUccQ/cNwLwYGMLnkiLBlsL++wTdk8bMGZw2n1jUay6H7xn6/R5t8EYR6OEe/wA/oiSfe4AtIesd0wk7kSBGj3tZNk/F73Or3MlYEK3jv/cHbBUOf+gdTPI0J/Xw4Oaan2EdVlYyX/lCXwsePdDz3/cUj7oloDcz0ZQUJ2pMxigQdhBbCaFzdQkXwKcTqOzvf4brNHvGaZTYFUf8AZzylY0OCMDf4QU6102KoF5C7utOy+IoyXwAiwHvfYV4G5OM8AOlEi1xqL4CFGFhoqPPUVrwwNFa1hYXxPFHZvgoDt6hRi1ui5j/Q2QZRGv1DRLZYn6tBlagbbTOmhg+16FOJ1ILC2nQSlMGHIuEcaxr9FZku+EwkSwZzJAs+E8nqjCew6JOIxOoxE0pSSDIx1aPgMw0+TBGmlxyRp9cdMi0wvmqI2kY84b+kWRLrizkSi+zo9r3ag9SNZYi1AV1T4BCcBVt4rPmkkmuE3GFFhr+GRfwpVUZYF0E1l1GVtQyeDUI3BHDPbHbKP2A1A+oE3ayizRHlNFziWa6EJW6twd3lgW62+3AfEFz7F/cIbi8nGpweTNBbwDuSLwABb26pncPuV6xYE/7OkhL8v4F+QIcZ8yetbxcKEB/uhO6bF95LOpmUMgtuVU2GXlSD+rNtSWrGsYP0BWFUROyLSq5YS5Dv5zuyhnR7JXPuUxEfSn1ZHm3OarKuFEnGFN+LQeAU4J3LbC+s2w5bSVRciezte9mQqpIx1sh4E5QKpkxgAfSnNI66mDukTYjKn89SziKA/kV0jBThnP6NxZ8wbvuXy4CJPv0c7AEsJHy32D/nDFOFhJ9WREqVom/oUH3AbFOWO1Dkav6aDgPgk9wTpAK+lr/EyYGvx8C3+0a3OTXgGzHwY4ZNBcucCLthWcsGu2PqS10mFj1yvK8RVcz1jb6q2w1u9Jwizl8D5QJRCvEdInSHNlAOf3hp915gLajRNcDvkMWrgmxzF1mTSLhjUlA4pRZHJSRYDWPmXSixdDEw/AzMY9AMb8kvomI8uGtiqDlPQtSsrsXFpp3cG4JYsO8f9yTjI46RPewLRUEEpBwHt2QKJBhA/ujf/+SujI/yW6PpdKTM/TdKZcfUZhzQHRsQXlAtfKxC+IoZ7Y5TCEHtsQEZR+zGdNZtyLrdhQxl/hmk02JlZCSFUiootVJrhtm01mqUOdMq/SeWrlEFfJ7Fq1DiVSxPwdyDwgqqKSbWH3ldD6rAKRFJ14AQZrZSm7YA7FYL7uRG0t1+AK86oDsAGESLKhJq7FAaUyBB/GFmP9lsAKfHrfGzJ55D7FZtyw3CEKGuP5MiwRYc2DO/BYsFyjZVMXRVOZAbKSHAQsfo9SAHJ+LJ2K/kgv5W1C8bwhFU2gSKsp5kDr9kOaOdxY5OkVEVTS7lhSikn+dWs99wftOHOlVup+6hCUPvpmJmrPltJGptwt8a/ifqIzSKez6uPT8lBwzgSsIIeg7TIAUX+Xzm21yX7uY5NWUujqKw3Qv6vH6XmeJsKYirpTGuYI268YV2NRvsWmWwa9lg1yuDXc8Gu1EZ7EY22M3KYDfTYE90buhiA8B9Fw5CqPQ5FHvbuVs5b9lGIhBSCIM/H+KkL0Dg2YPQdBG0VFUnj0vX86DZPizyqdz4UcRcFL3y2asHIQHEWOnhnAlsaEtTNfhFGPTgZM3bob64GMrOlDgVs132yE3ADOJtYvySYg7mItQBKFxvgFnUn5yGjPLRbyriSgXJqUrKj6WlRSkCbbbM/JBbWea1MjLzI3NlmdfLyMwP4JVl3igjMz/OV5Z5M09mlXQwFafNCvHTORH5mQBWc5pZoSjQigvbyjpbrVbXMFlN73brendZ+abhDaWPICiJ9vcj1rnfnKBcOqLCE8c1Q29bKeUrq9usJgXTN258NOmbROycYdssHXUvwCW/QzNpKD2GBotm2KidGbnjaWrUWFcb+i52xjEp4i9SXzB4OkPJOrhwlVIrla1FG4UcvdXwNdJM8qFEVtoFlkmfPgFrxuslanC/hyv3pjiPCuhL1wqZKFD9SFPqv9JleBzMyLBdmSxlBGb5MdGfwyc2eSwlR9oIXnYyFtCWGI35lMWzMZ+ueDjm0+VPx4CmZEc7prx0S1uAumxPO4aabHwT80/UFE+yT68rnoE8hbZ4BvIU+uIZyNNpjMfgEwz5MPb9X8Z0FGSr9+8xYE4+3gNuVQNf4XpSiz1ik7v1Cr9KsZV5PQB0wtxabQVpVA0Mqsmw8M9lX08C6WXmIcA+zXmpDDfpwBRQSj5kECgrvGQQuEo/ZRB5yr1l8DkuNZ7NhprWCBmQM2YdIXTZSUcKKRx25AGppi9Um5R54xsTlnk5LMEmezCKx71J9OSrWcUDYmCZcAwXcV5iBhhhpLaw7AQtQrjcNDCCSXpA2WEgVvyqyVd0F82ZeyV4kyrkzdvQg0u95xYYLv8qOAC55MtZBcp03vUGwJd5tQwQOnfwQZShd2X74P0XGPz4H4/rvG3x+yS0L21W6+jcZuKS9oLMRBMpSsqKbm/wBFqylGEuS5YR301jyaORTjuwNqG9joWLtWZ2LV1MsHRNo+h75GvwLQpEqwh8puXcDr89ER9sjNLy0+ORdD10R5/effovPRAbe8EXLT6dJndc/Vga40bha2ksWxynd4dz6QnDnm+HMb1heC8O46LaMtXDdnO/soAmmPA7C6plpp90J78+U+pZN0Crvg8hfLPhAPxxC78GgNkz9d0GjHPgkyxVQ4AFzykLw5bRFr4NCx90ayiwB97up9MrGg6Iw23x2ycDOYoiuny1L4mMTAmceqK/UFbHoyp9COMulycRiS+N4ZKPNKNFVE+f/g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Колонка = _t, Тип = _t, Название = _t]),

    fields_values = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVXNTttAEH6VKOcKqT8nbpTSnpCqEqmqEIo29sasYnuj9TohtyRAWwkkJMSpglL6AqQ0aVMg4RV236gza0PiEG9QpNgz2u/bb8bzs7lZbAXL0bJPIlkSpFplzgaPhUOLz4rEhT/1Qw3UtbpRPXWresWtZxaAywR1JILOdVsfAWCs/hXUlRqqv2qou3Y0CyUVIfERf6xGeld3dRsuH8FvqAYFdacG6ND7QNvXB3Y2LjwSMgfJvgPqN+gHGv0F8B2IZ4jC7oBoiKadStAqFSIRBtnQe0ZHT93oQ+Ds6aOC7sDvAJJ0vShFEWlQk9YLNdb7hmaUxohEQDAGWUN9tICGOywRhDyfIZxUDqgYgBxQ2FXDLMcGJcLZXgs9FiJFhTA3LkfGCeZrNJccHthBLPSmMGAthHicez4tB7zC4EHqdfC9M77CuvEVVsD3BIaHaxP0wosDwvwJaB2sJRFbEYIEFZ+KCehD4liEk7EsV1rwUorlErzYzrbINueTGz6huTCUFgldulNmAfFohF/9Euqlj42pOwX1DUqnjd8bnNcLvnrKFJB6Ls9sZ+UxiBqVjzhOkQNUDGa7PYfFVMAclrH6BfHcl3R+r9po02KzUMM7zhVQDA4w/phWhGHzlEtC2nycwzNguYKW7Czsv5TloRKyPOfzw11xG7NtQdwmF2406aoVMHJBkgiPyrLpDahq6JJWybjyIXFYC3kzRIk4qnCWX+EUhQghgfm4Ro2HktQkTQY6TrYRYHoQYHc2w9O4FilPFkkmKSeQkrYxulZ4DJmtguWmmgs4VA0SqW7MR54S7tIGc+gqkdTjAtv4OeJOkk0BWgFsOfwimcK3afPsYZiW4y/NejT7dKS/Zvvk0eFXePinOs5G+8Zk571PZJWLoNSqY9irkGy6I5MddaEunwDZeCi9OdUWUgl1VUuPOtT3Y5+I+43TNWWerD6MQR/mIanchq1uJsXa/WvO0ZCbD2ivsiyEIyVi+pD8Xdjy4+nKyp5tsnKVwfMje8vs25Vie6Bg87QeDWgU0dBLRJwawR1TX30cJrptR2cK9Wxe2Fv/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Параметр = _t, Значение = _t, Перевод = _t]),

    TranslationList = List.Buffer(
        Table.ToRows(
            Table.SelectRows(
                fields_values, 
                each List.Contains(
                    Table.ColumnNames(raw_data),[Параметр])
                )
            )
    ),
    Translated_values = List.Accumulate(
        TranslationList,
        raw_data,
        (t,r) => Table.ReplaceValue(t,r{1},r{2},Replacer.ReplaceText,{r{0}}) 
    ),


    columns= Table.TransformColumns(
        Table.SelectRows(column_names, each List.Contains(Table.ColumnNames(raw_data),[Колонка])),
        {{"Тип", each Expression.Evaluate(if Text.EndsWith(_,"Type") then _ else "type "&_)}}
    ),

    makeList = (val as text) => Text.Trim(val,{"[","]"}),
    #"Changed Type" = Table.TransformColumnTypes(Translated_values,Table.ToRows(Table.SelectColumns(columns,{"Колонка","Тип"}))),
    #"Trimmed square brackets" = Table.TransformColumns(
        #"Changed Type", {
            {"ym:s:watchIDs", each makeList(_)}, // 
            {"ym:s:goalsID", each makeList(_)},
            {"ym:s:goalsDateTime", each makeList(_)}
        }, null, MissingField.Ignore
    ),
    
    #"Renamed Columns" = Table.RenameColumns( #"Trimmed square brackets",List.Buffer(Table.ToRows(Table.SelectColumns(column_names,{"Колонка","Название"}))),MissingField.Ignore)
in
    #"Renamed Columns"