let
Источник = Folder.Files("G:..."),
SelectRowsTmp = Table.SelectRows(Источник, each not Text.Contains([Name], "$")),
SelectRowsXl = Table.SelectRows(SelectRowsTmp, each Text.Contains([Extension], "xl")),
SplitColumnXl = Table.SplitColumn(
SelectRowsXl,
"Name",
Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),
{"Name", "Name.1"}
),
JoinN = Table.NestedJoin(
SplitColumnXl,
{"Name"},
#"Планограммы исходники (2)",
{"Планограмма"},
"Планограммы исходники (2)",
JoinKind.LeftOuter
),
ExpandN = Table.ExpandTableColumn(
JoinN,
"Планограммы исходники (2)",
{"Планограмма"},
{"Планограмма"}
),
SelectRowsNoNullP = Table.SelectRows(ExpandN, each ([Планограмма] <> null)),
SelectColumnsCN = Table.SelectColumns(
SelectRowsNoNullP,
{"Content", "Name", "Планограмма", "Date modified"}
),
AddColumnSheetLst = Table.AddColumn(
SelectColumnsCN,
"Пользовательский",
each Table.SelectRows(Excel.Workbook([Content]), each [Name] = "Списком")
),
ExpandT = Table.ExpandTableColumn(
AddColumnSheetLst,
"Пользовательский",
{"Name", "Data"},
{"Name.1", "Data"}
),
SelectRowsSheetLst = Table.SelectRows(ExpandT, each ([Name.1] = "Списком")),
AddColumnSL = Table.AddColumn(SelectRowsSheetLst, "Строк", each Table.RowCount([Data])),
ExpandD = Table.ExpandTableColumn(
AddColumnSL,
"Data",
{
"Column1",
"Column2",
"Column3",
"Column4",
"Column5",
"Column6",
"Column7",
"Column8",
"Column9",
"Column10"
},
{
"Column1",
"Column2",
"Column3",
"Column4",
"Column5",
"Column6",
"Column7",
"Column8",
"Column9",
"Column10"
}
),
AddColumnL = Table.AddColumn(
ExpandD,
"Длина1234",
each Text.Length(Text.From([Column1]))
+ Text.Length(Text.From([Column2]))
+ Text.Length(Text.From([Column3]))
+ Text.Length(Text.From([Column4]))
),
SelectRowsNoNullL = Table.SelectRows(AddColumnL, each ([Длина1234] <> null)),
ReplaceSp = Table.ReplaceValue(SelectRowsNoNullL, " ", "", Replacer.ReplaceText, {"Column3"}),
AddColumnSla = Table.AddColumn(ReplaceSp, "Слеш", each Text.Split([Column3], "/")),
ExpandLSla = Table.ExpandListColumn(AddColumnSla, "Слеш"),
DuplicateSla = Table.DuplicateColumn(ExpandLSla, "Слеш", "Копия Слеш"),
TypesSla = Table.TransformColumnTypes(DuplicateSla, {{"Копия Слеш", Int64.Type}}),
RemoveRError = Table.RemoveRowsWithErrors(TypesSla, {"Копия Слеш"}),
SelectColumnsNPDN = Table.SelectColumns(
RemoveRError,
{
"Name",
"Планограмма",
"Date modified",
"Name.1",
"Column1",
"Column2",
"Column3",
"Column4",
"Слеш"
}
)
in
SelectColumnsNPDN
|