Power Query «COUNTIFS» загружается долго для больших данных

У меня есть данные, как показано ниже. Реальные данные намного больше и содержат больше столбцов. Это записи о бронировании отелей.

и я хочу, чтобы он был суммирован, чтобы каждый день показывать, сколько номеров каждого типа забронировано, с выводом, как показано ниже:

С такими небольшими данными приведенный ниже код в Power Query работает хорошо. Однако с реальными данными, содержащими около 16 тыс. строк, загрузка данных занимает более получаса.

let
    Source = List.Dates(Date.From(List.Min(Bookings[Arrival Date])),Duration.Days(DateTime.Date(DateTime.LocalNow()) - Date.From(List.Min(Bookings[Arrival Date]))) + 1,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Standard" = Table.AddColumn(#"Changed Type", "Standard", each List.Count(
    Table.SelectRows(
        Bookings,
        (Standard) => Standard[Arrival Date] <= [Date]
        and
        Standard[Departure Date]>[Date]
        and
        Standard[Room Type] = "Standard"
    )[Booking Number]
)),
    #"Added Deluxe" = Table.AddColumn(#"Added Standard", "Deluxe", each List.Count(
    Table.SelectRows(
        Bookings,
        (Deluxe) => Deluxe[Arrival Date] <= [Date]
        and
        Deluxe[Departure Date]>[Date]
        and
        Deluxe[Room Type] = "Deluxe"
    )[Booking Number]
)),
    #"Added Suite" = Table.AddColumn(#"Added Deluxe", "Suite", each List.Count(
    Table.SelectRows(
        Bookings,
        (Suite) => Suite[Arrival Date] <= [Date]
        and
        Suite[Departure Date]>[Date]
        and
        Suite[Room Type] = "Suite"
    )[Booking Number]
)),
    #"Changed to Number" = Table.TransformColumnTypes(#"Added Suite",{{"Standard", Int64.Type}, {"Deluxe", Int64.Type}, {"Suite", Int64.Type}})
in
    #"Changed to Number"

Есть ли другие решения, которые работают для больших данных.

excel powerquery countif

12.03.2024 08:32

Преобразование HTML-таблицы в профессиональный документ Excel

Это самый простой способ создания Excel из HTML-таблицы.

Импорт excel в laravel в базу данных

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

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Я предполагаю, что когда вы пишете «загрузка данных», вы на самом деле имеете в виду выполнение всего запроса, а не только загрузку данных в запрос.

Table.SelectRows относительно медленно.

Это должно работать быстрее. Если это так на ваших фактических данных, то можно легко внести любые необходимые незначительные корректировки.

Добавьте столбец с полным списком дат для каждого бронирования.
- включая дату прибытия, но не дату отъезда
Разверните этот столбец даты, чтобы у нас была одна запись для каждой даты/типа_комнаты.
Группировать по дате и типу комнаты и суммировать с помощью функции Count.
Поверните функцию room_type с помощью агрегирования SUM в столбце Count.
Измените порядок столбцов и замените нули нулями (0).

Обратите внимание, что этот алгоритм считает даты прибытия, но не даты отъезда. Если это не так, это можно легко изменить.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bookings"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Booking Number", Int64.Type}, {"Arrival Date", type date}, {"Departure Date", type date}, {"Room Type", type text}}),
    
//add column with all of the dates for each booking
//This will include the arrival but not the departure date
//easy to adjust if needed
    #"All Dates" = Table.AddColumn(#"Changed Type", "Dates", each List.Dates([Arrival Date], 
                         Duration.Days([Departure Date]-[Arrival Date]), 
                         #duration(1,0,0,0)), type {date}),

//expand to one entry per date
    #"Expanded allDates" = Table.ExpandListColumn(#"All Dates", "Dates"),

//Adjust Room Types for minor typos
    #"Adjust Room Types" = Table.TransformColumns(#"Expanded allDates", {"Room Type", each Text.Proper(Text.Trim(_))}),

//Group by dates and room type
    #"Grouped Rows" = Table.Group(#"Adjust Room Types", {"Dates", "Room Type"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

//then pivot
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Room Type"]), 
        "Room Type", "Count", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Dates", "Standard", "Deluxe", "Suite"}),
    #"Replace nulls with zero" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"Standard", "Deluxe", "Suite"})
in
    #"Replace nulls with zero"

Данные

Результаты

12.03.2024 12:12

Если выходные данные могут быть сводной таблицей, я бы загрузил организационную таблицу (без изменений в PQ) в DataModel и создал 3 показателя. например: CountStandard = CALCULATE(COUNTA([type]);Table1[type] = "Standard")

затем перетащите 3 меры в поле значения сводной точки. Результат будет моментально.

Демо-файл

12.03.2024 15:31