let
Source = Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=431616&action=download"),
Custom1 = Csv.Document(Source,null,"0, 10, 26, 41, 57, 72, 85, 99, 102, 116",null,1251),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Custom1, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Split Column by Position1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", Splitter.SplitTextByPositions({0, 10, 26, 40, 56, 71, 84, 98, 102, 116}, false), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
#"Cleaned Text" = Table.TransformColumns(#"Split Column by Position1",{{"Column1.1", Text.Clean, type text}, {"Column1.2", Text.Clean, type text}, {"Column1.3", Text.Clean, type text}, {"Column1.4", Text.Clean, type text}, {"Column1.5", Text.Clean, type text}, {"Column1.6", Text.Clean, type text}, {"Column1.7", Text.Clean, type text}, {"Column1.8", Text.Clean, type text}, {"Column1.9", Text.Clean, type text}, {"Column1.10", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}, {"Column1.3", Text.Trim, type text}, {"Column1.4", Text.Trim, type text}, {"Column1.5", Text.Trim, type text}, {"Column1.6", Text.Trim, type text}, {"Column1.7", Text.Trim, type text}, {"Column1.8", Text.Trim, type text}, {"Column1.9", Text.Trim, type text}, {"Column1.10", Text.Trim, type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Количество] <> "" and [Количество] <> "------------")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Дата опц.", type date}, {"Позиция", Int64.Type}, {"Описание", type text}, {"Тип источника", type text}, {"Источник", Int64.Type}, {"Скл. подр.", type text}, {"Количество", type number}, {"ЕИ", type text}, {"Уд.себестоим.", type number}, {"Стоимость опц.", type number}})
in
#"Changed Type" |