Groupy by a term and dates does not group the exact words from the term

[CK_ax]

Hello everyone,

I am quite new to Power Query and use it in Excel.

I have a data collection in which I manage seminars.
Within the data collection, I first group by the column "Begriff" and then by "Beginnt am". "Begriff" contains the seminar name and "Beginnt am" is the start date.
When grouping, I have the data grouped so that the periods that are within a week are grouped together.
It works almost well.
Unfortunately, with some groups, the content of "Begriff" is not exactly the same. The first characters are the same, but later they differ from each other. The periods are directly after each other within the same week.

How can I ensure that the exact same content is grouped in the "Term" column?

Below is the code:

= Table.Group(#"Gefilterte Zeilen4", {"Betreff", "Beginnt am"}, {{"Seminardauer", each Table.RowCount(_), Int64.Type}, {"Beschreibung", each List.Min([Beschreibung]), type nullable text}, {"Details", each _, type table [Betreff=nullable text, #"Seminar-Bezeichnung"=text, Consultant=text, Firma=nullable text, Status=text, AnzahlTeilnehmer=nullable number, Beginnt am=nullable date, Beginnt um=nullable time, Endet am=nullable date, Endet um=nullable time, #"Besprechungsorganisator*in"=nullable text, Erforderliche Teilnehmer=nullable text, Beschreibung=nullable text, Kategorien=nullable text, Ort=nullable text]}},
GroupKind.Local, (x, y) => Value.Compare(Date.StartOfWeek(x[Beginnt am]), Date.StartOfWeek(y[Beginnt am])))

[dufoq3 · Accepted solution]

Hi @CK_ax, you are using GroupKind.Local with 5th parameter of Table.Group (so I hope you know what you are doing at this step). If you want to use GroupKind.Local and you want to consider [Betreff] with Date.StartOfWeek([Beginnt am]) - it should be like this:

= Table.Group(#"Geänderter Typ", {"Betreff", "Beginnt am"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}, {"Details", each _, type table [Betreff=nullable text, Beginnt am=nullable date, Endet am=nullable date, Kategorien=nullable text]}},
    GroupKind.Local,
    (x, y) => [ a = {Value.Compare(Date.StartOfWeek(x[Beginnt am]), Date.StartOfWeek(y[Beginnt am])), Value.Compare(x[Betreff], y[Betreff])},
                b = if List.ContainsAll({0}, { a{0}, a{1} }) then 0 else 1
              ][b])

Whole Code:

Don't forget to change addres to your source excel file

let
    Quelle = Excel.Workbook(File.Contents("c:\Downloads\PowerQueryForum\CK_ax\Sampel.xlsx"), null, true),
    #"Seminare 2024_Juni_Sheet" = Quelle{[Item="Seminare 2024_Juni",Kind="Sheet"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Seminare 2024_Juni_Sheet", [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Betreff", type text}, {"Beginnt am", type date}, {"Endet am", type date}, {"Kategorien", type text}}),
    #"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ", {"Betreff", "Beginnt am"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}, {"Details", each _, type table [Betreff=nullable text, Beginnt am=nullable date, Endet am=nullable date, Kategorien=nullable text]}},
    GroupKind.Local,
    (x, y) => [ a = {Value.Compare(Date.StartOfWeek(x[Beginnt am]), Date.StartOfWeek(y[Beginnt am])), Value.Compare(x[Betreff], y[Betreff])},
                b = if List.ContainsAll({0}, { a{0}, a{1} }) then 0 else 1
              ][b])
in
    #"Gruppierte Zeilen"

[CK_ax]

thank you very much.

I am a beginner and watched some tutorials to get to this point.

But I guess I need to delve deeper.

Thank you very much for your help the information.

best regards.

[dufoq3]

You're welcome.

[CK_ax]

Of course I can provide a sample.

Here you find the Sample.xlsx-File with sample data:

In the Import.xlsx I´ve imported and grouped the data.

Here´s the whole code:

let

    Quelle = Excel.Workbook(File.Contents("C:\Users\localUser\Downloads\Sampel.xlsx"), null, true),

    #"Seminare 2024_Juni_Sheet" = Quelle{[Item="Seminare 2024_Juni",Kind="Sheet"]}[Data],

    #"Höher gestufte Header" = Table.PromoteHeaders(#"Seminare 2024_Juni_Sheet", [PromoteAllScalars=true]),

    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Betreff", type text}, {"Beginnt am", type date}, {"Endet am", type date}, {"Kategorien", type text}}),

    #"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ", {"Betreff", "Beginnt am"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}, {"Details", each _, type table [Betreff=nullable text, Beginnt am=nullable date, Endet am=nullable date, Kategorien=nullable text]}},

    GroupKind.Local,

    (x, y) => Value.Compare(Date.StartOfWeek(x[Beginnt am]), Date.StartOfWeek(y[Beginnt am]))

)

in

    #"Gruppierte Zeilen"

In the following picture, you can see that there are two different texts in the attached table, although  the texts should be exactly the same after grouping.

[WanderingBI]

Could you provide a minimal example of your data collection? And the full Power Query code instead of a snippet?

Then it would be easier to check for a solution.