power query водный отчет

Дата: Пятница, 11.10.2024, 21:33 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

2019

всем привет,пытаюсь создать сводный отчет из отдельных файлов с футбольными командами.
В столбцах A,B,C даты матчей и названия команд.Они во всех файлах всегда в этих столбцах.
Начиная со столбца D идут числовые коэффициенты для этих команд (заголовки первая и вторая строка).Например D1 и D2 это заголовок Winline 1.
Проблема в том что в разных файлах эти данные в разных столбцах,но заголовки те же и всегда первой и второй строках.
Я пробовал решить эту проблему через пункт сделать первую строку заголовком в Power query ,но при создании сводного отчета цифры коэффициентов из разных столбцов перемешались.
Помогите пжлста сделать корректный отчет через power query или др способом чтобы все собиралось четко без ошибок.
Т.Е.Мне нужна такая же структура как в моем сводном :дата,команда из стоблца home,команда из стоблца guest,имя файла откуда взяты данные,букмекеры и их коэффициенты.

К сообщению приложен файл: irlandija_vysshaja_liga_01_08_.xlsx (94.9 Kb) · norvegija_obos_liga_04_10_2024.xlsx (458.8 Kb) · norvegija_obos_liga_18_07_2024.xlsx (131.5 Kb) · svodnyj.xlsx (295.5 Kb)

Дата: Суббота, 12.10.2024, 13:58 | Сообщение № 2

Группа: Пользователи

Ранг: Новичок

Сообщений: 28

Репутация: 8 ±

Замечаний: 0% ±

365

Дополните список имен (у вас их 11) руками или прочитайте из какого-нибудь файла, а также поменяйте путь к папке с файлами - все в комментариях в коде.
[vba]

Код

let
    fx = (file) =>
        [s = Excel.Workbook(file, null, true){[Item="1x2",Kind="Sheet"]}[Data],
        skip = Table.RemoveLastN(s, (x) => x[Column2] is null),
        pzz = List.Buffer({0, 1, 2} & List.PositionOf(Record.ToList(skip{0}), null, Occurrence.All, (c, v) => c <> v)),
        lst = List.Buffer(Table.ToList(skip, (x) => List.Transform(pzz, (i) => x{i}))),
        headers = List.Transform(
            List.Zip({List.Skip(lst{0}, 3), List.Transform(List.Skip(lst{1}, 3), Text.From)}),
            (x) => Text.Combine(x, " ")
        ),
        to_table = Table.FromRows(List.Skip(lst, 2), {"date", "home", "guest"} & headers)][to_table],
    // column names
    names = {"winline", "melbetru"}, // дополните список руками или прочитайте из файла
    columns = {"date", "home", "guest"} & List.Transform(
        List.Zip(
            {
                List.Combine(List.Transform(names, (x) => List.Repeat({x}, 3))),
                List.Repeat({" 1", " X", " 2"}, List.Count(names))
            }
        ),
        Text.Combine
    ),
    files = Table.SelectRows(
        Folder.Files("ПУТЬ_К_ПАПКЕ_С_ФАЙЛАМИ"),
        (x) => x[Attributes][Kind] = "Excel File" and x[Attributes][Hidden] = false
    )[[Name], [Content]],
    tx_files = Table.TransformColumns(files, {"Content", fx}),
    z = Table.ExpandTableColumn(tx_files, "Content", columns)
in
    z

[/vba]

Сообщение отредактировал AlienSphinx - Суббота, 12.10.2024, 16:01

СообщениеДополните список имен (у вас их 11) руками или прочитайте из какого-нибудь файла, а также поменяйте путь к папке с файлами - все в комментариях в коде.
[vba]

Код

[/vba]

Автор - AlienSphinx
Дата добавления - 12.10.2024 в 13:58

Дата: Суббота, 12.10.2024, 14:41 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

2019

Цитата AlienSphinx, 12.10.2024 в 13:58, в сообщении № 2 (

)

Дополните список имен (у вас их 11) руками или прочитайте из какого-нибудь файла, а также поменяйте путь к папке с файлами - все в комментариях в коде.

Спасибо,вроде работает,а в какую строку кода прописать колонки date,home,guest ?они не выгружаются

К сообщению приложен файл: svodnyj_obnovlennyj.xlsx (184.6 Kb)

Дата: Суббота, 12.10.2024, 16:02 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 28

Репутация: 8 ±

Замечаний: 0% ±

365

Цитата snegovik2389, 12.10.2024 в 14:41, в сообщении № 3 (

)

в какую строку кода прописать колонки date,home,guest ?

в строку columns. Поправил код в сообщении.

Дата: Понедельник, 14.10.2024, 20:16 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

2019

Цитата AlienSphinx, 12.10.2024 в 16:02, в сообщении № 4 (

)

в строку columns. Поправил код в сообщении.

спасибо вам большое все работает.

Скажите пжлста еще такую вещь,у меня в этих файлах с матчами несколько листов с букмекерами,если я в будущем например захочу выгрузить данные с др листа (например Тоталы) ,то где мне это прописать в коде ?

Дата: Вторник, 15.10.2024, 20:05 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 28

Репутация: 8 ±

Замечаний: 0% ±

365

Цитата snegovik2389, 14.10.2024 в 20:16, в сообщении № 5 (

)

например Тоталы

Например в тоталах у вас конкретная грязь в самом конце таблицы (530-е колонки, файл irlandija_vysshaja_liga). Как-то не очень хочется в этой грязи возиться. Придумайте свой способ или действуйте по аналогии с моим кодом, но придется от грязи избавляться.

Дата: Суббота, 16.11.2024, 10:03 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

2019

AlienSphinx, хорошо спасибо.Подскажите пжлста еще.Потребовалось добавить значения из колонок result,result,winner с этого же листа (столбцы sk,sl,sm).НО когда пытаюсь их добавить почему-то ошибка.Как сделать чтобы данные с этих 3 столбцов корректно выгрузились в самом правом крае моей сводной таблицы power query ?

Вот мой код

[vba]

Код

let
fx = (file) =>
[s = Excel.Workbook(file, null, true){[Item="1x2",Kind="Sheet"]}[Data],
skip = Table.RemoveLastN(s, (x) => x[Column2] is null),
pzz = List.Buffer({0, 1, 2} & List.PositionOf(Record.ToList(skip{0}), null, Occurrence.All, (c, v) => c <> v)),
lst = List.Buffer(Table.ToList(skip, (x) => List.Transform(pzz, (i) => x{i}))),
headers = List.Transform(
List.Zip({List.Skip(lst{0}, 3), List.Transform(List.Skip(lst{1}, 3), Text.From)}),
(x) => Text.Combine(x, " ")
),
to_table = Table.FromRows(List.Skip(lst, 2), {"date", "home", "guest","result","result","winner"} & headers)][to_table],
// column names
names = {"winline", "fonbet","betboom"}, // дополните список руками или прочитайте из файла
columns = {"date", "home", "guest","result","result","winner"} & List.Transform(
List.Zip(
{
List.Combine(List.Transform(names, (x) => List.Repeat({x}, 3))),
List.Repeat({" 1", " X", " 2"}, List.Count(names))
}
),
Text.Combine
),
files = Table.SelectRows(
Folder.Files("D:\bet to odd\четверг\scores24"),
(x) => x[Attributes][Kind] = "Excel File" and x[Attributes][Hidden] = false
)[[Name], [Content]],
tx_files = Table.TransformColumns(files, {"Content", fx}),
z = Table.ExpandTableColumn(tx_files, "Content", columns),
#"Измененный тип" = Table.TransformColumnTypes(z,{{"date", type date}}),
#"Обрезанный текст" = Table.TransformColumns(#"Измененный тип",{{"home", Text.Trim, type text}, {"guest", Text.Trim, type text}}),
result = #"Обрезанный текст"{0}[result]
in
result

[/vba]

как на этом форуме вставить код отдельной записью как в сообщениях выше, чтобы его было удобней скопировать?

Код

let
fx = (file) =>
[s = Excel.Workbook(file, null, true){[Item="1x2",Kind="Sheet"]}[Data],
skip = Table.RemoveLastN(s, (x) => x[Column2] is null),
pzz = List.Buffer({0, 1, 2} & List.PositionOf(Record.ToList(skip{0}), null, Occurrence.All, (c, v) => c <> v)),
lst = List.Buffer(Table.ToList(skip, (x) => List.Transform(pzz, (i) => x{i}))),
headers = List.Transform(
List.Zip({List.Skip(lst{0}, 3), List.Transform(List.Skip(lst{1}, 3), Text.From)}),
(x) => Text.Combine(x, " ")
),
to_table = Table.FromRows(List.Skip(lst, 2), {"date", "home", "guest","result","result","winner"} & headers)][to_table],
// column names
names = {"winline", "fonbet","betboom"}, // дополните список руками или прочитайте из файла
columns = {"date", "home", "guest","result","result","winner"} & List.Transform(
List.Zip(
{
List.Combine(List.Transform(names, (x) => List.Repeat({x}, 3))),
List.Repeat({" 1", " X", " 2"}, List.Count(names))
}
),
Text.Combine
),
files = Table.SelectRows(
Folder.Files("D:\bet to odd\четверг\scores24"),
(x) => x[Attributes][Kind] = "Excel File" and x[Attributes][Hidden] = false
)[[Name], [Content]],
tx_files = Table.TransformColumns(files, {"Content", fx}),
z = Table.ExpandTableColumn(tx_files, "Content", columns),
#"Измененный тип" = Table.TransformColumnTypes(z,{{"date", type date}}),
#"Обрезанный текст" = Table.TransformColumns(#"Измененный тип",{{"home", Text.Trim, type text}, {"guest", Text.Trim, type text}}),
result = #"Обрезанный текст"{0}[result]
in
result

[/vba]

как на этом форуме вставить код отдельной записью как в сообщениях выше, чтобы его было удобней скопировать?snegovik2389

Сообщение отредактировал snegovik2389 - Суббота, 16.11.2024, 10:11

СообщениеAlienSphinx, хорошо спасибо.Подскажите пжлста еще.Потребовалось добавить значения из колонок result,result,winner с этого же листа (столбцы sk,sl,sm).НО когда пытаюсь их добавить почему-то ошибка.Как сделать чтобы данные с этих 3 столбцов корректно выгрузились в самом правом крае моей сводной таблицы power query ?

Вот мой код

[vba]

Код

let
fx = (file) =>
[s = Excel.Workbook(file, null, true){[Item="1x2",Kind="Sheet"]}[Data],
skip = Table.RemoveLastN(s, (x) => x[Column2] is null),
pzz = List.Buffer({0, 1, 2} & List.PositionOf(Record.ToList(skip{0}), null, Occurrence.All, (c, v) => c <> v)),
lst = List.Buffer(Table.ToList(skip, (x) => List.Transform(pzz, (i) => x{i}))),
headers = List.Transform(
List.Zip({List.Skip(lst{0}, 3), List.Transform(List.Skip(lst{1}, 3), Text.From)}),
(x) => Text.Combine(x, " ")
),
to_table = Table.FromRows(List.Skip(lst, 2), {"date", "home", "guest","result","result","winner"} & headers)][to_table],
// column names
names = {"winline", "fonbet","betboom"}, // дополните список руками или прочитайте из файла
columns = {"date", "home", "guest","result","result","winner"} & List.Transform(
List.Zip(
{
List.Combine(List.Transform(names, (x) => List.Repeat({x}, 3))),
List.Repeat({" 1", " X", " 2"}, List.Count(names))
}
),
Text.Combine
),
files = Table.SelectRows(
Folder.Files("D:\bet to odd\четверг\scores24"),
(x) => x[Attributes][Kind] = "Excel File" and x[Attributes][Hidden] = false
)[[Name], [Content]],
tx_files = Table.TransformColumns(files, {"Content", fx}),
z = Table.ExpandTableColumn(tx_files, "Content", columns),
#"Измененный тип" = Table.TransformColumnTypes(z,{{"date", type date}}),
#"Обрезанный текст" = Table.TransformColumns(#"Измененный тип",{{"home", Text.Trim, type text}, {"guest", Text.Trim, type text}}),
result = #"Обрезанный текст"{0}[result]
in
result

Автор - snegovik2389
Дата добавления - 16.11.2024 в 10:03

Дата: Понедельник, 18.11.2024, 21:24 | Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 28

Репутация: 8 ±

Замечаний: 0% ±

365

Цитата

почему-то ошибка

какая ошибка? Ну в "исправленном" коде что-то накосячили по неведению. Разбирайтесь, исправляйте.

Дата: Вторник, 19.11.2024, 22:12 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

2019

AlienSphinx, начал разбираться и копаться в коде.Вы писали выше что мы указываем нужные столбцы в строке кода columns,я добавил туда столбцы result,result,winner.
получился такой код
[vba]

Код

let
fx = (file) =>
[s = Excel.Workbook(file, null, true){[Item="1x2",Kind="Sheet"]}[Data],
skip = Table.RemoveLastN(s, (x) => x[Column2] is null),
pzz = List.Buffer({0, 1, 2} & List.PositionOf(Record.ToList(skip{0}), null, Occurrence.All, (c, v) => c <> v)),
lst = List.Buffer(Table.ToList(skip, (x) => List.Transform(pzz, (i) => x{i}))),
headers = List.Transform(
List.Zip({List.Skip(lst{0}, 3), List.Transform(List.Skip(lst{1}, 3), Text.From)}),
(x) => Text.Combine(x, " ")
),
to _table = Table.FromRows(List.Skip(lst, 2), {"date", "home", "guest"} & headers)][to_table],
// column names
names = {"winline", "melbetru"}, // дополните список руками или прочитайте из файла
columns = {"date", "home", "guest","result","result","winner"} & List.Transform(
List.Zip(
{
List.Combine(List.Transform(names, (x) => List.Repeat({x}, 3))),
List.Repeat({" 1", " X", " 2"}, List.Count(names))
}
),
Text.Combine
),
files = Table.SelectRows(
Folder.Files("ПУТЬ_К_ПАПКЕ_С_ФАЙЛАМИ"),
(x) => x[Attributes][Kind] = "Excel File" and x[Attributes][Hidden] = false
)[[Name], [Content]],
tx_files = Table.TransformColumns(files, {"Content", fx}),
z = Table.ExpandTableColumn(tx_files, "Content", columns)
in
z

[/vba]

при запуске такого кода появляются столбцы result,result,winner но все значения там пустые ( в источнике данных они есть)

я решил что нужно добавить еще значению в строку кода для заголовков to_table = Table.FromRows ,но если прописываю там колонки result,result,winner выдает ошибку что поле result уже существует (у меня 2 таких столбцах с одинаковым названием,но с разными данными)

дайте пжлста наводку на правильный путь действий

Код

let
fx = (file) =>
[s = Excel.Workbook(file, null, true){[Item="1x2",Kind="Sheet"]}[Data],
skip = Table.RemoveLastN(s, (x) => x[Column2] is null),
pzz = List.Buffer({0, 1, 2} & List.PositionOf(Record.ToList(skip{0}), null, Occurrence.All, (c, v) => c <> v)),
lst = List.Buffer(Table.ToList(skip, (x) => List.Transform(pzz, (i) => x{i}))),
headers = List.Transform(
List.Zip({List.Skip(lst{0}, 3), List.Transform(List.Skip(lst{1}, 3), Text.From)}),
(x) => Text.Combine(x, " ")
),
to _table = Table.FromRows(List.Skip(lst, 2), {"date", "home", "guest"} & headers)][to_table],
// column names
names = {"winline", "melbetru"}, // дополните список руками или прочитайте из файла
columns = {"date", "home", "guest","result","result","winner"} & List.Transform(
List.Zip(
{
List.Combine(List.Transform(names, (x) => List.Repeat({x}, 3))),
List.Repeat({" 1", " X", " 2"}, List.Count(names))
}
),
Text.Combine
),
files = Table.SelectRows(
Folder.Files("ПУТЬ_К_ПАПКЕ_С_ФАЙЛАМИ"),
(x) => x[Attributes][Kind] = "Excel File" and x[Attributes][Hidden] = false
)[[Name], [Content]],
tx_files = Table.TransformColumns(files, {"Content", fx}),
z = Table.ExpandTableColumn(tx_files, "Content", columns)
in
z

Сообщение отредактировал snegovik2389 - Вторник, 19.11.2024, 22:13