Group table in terms of date column and specific value of another column

[Vibration85]

Hello all,

I have a problem which sounds so easy but as I'm a newbie I can't manage it and need your expertise to help me please:

As a sample you can have a look at the table below:

I need to remove all the rows which have not Main Category value on their date (in this example I should remove rows 9,10,11), The main category value here is the beginning point for my process per day and if it does not exist in a day, I should remove all the other records on that specific date or ignore them

could you please let me know how can I make a DAX code or M-query to fix it? as my data are thousands or millions, needs to have the fastest one.

Many thanks 

[DataInsights · Accepted solution]

@Vibration85,

Try this solution in Power Query.

1. Create table MainCategoryDates, consisting of distinct dates for "Main Category" rows.

let
    Source = OriginalTable,
    FilterRows = Table.SelectRows(Source, each ([Main column] = "Main Category")),
    RemoveColumns = Table.SelectColumns(FilterRows,{"Date"}),
    RemoveDuplicates = Table.Distinct(RemoveColumns)
in
    RemoveDuplicates

2. Merge table MainCategoryDates with the data table using an inner join on the Date column.

let
    Source = Table.NestedJoin(OriginalTable, {"Date"}, MainCategoryDates, {"Date"}, "MainCategoryDates", JoinKind.Inner),
    RemoveColumns = Table.RemoveColumns(Source,{"MainCategoryDates"})
in
    RemoveColumns

3. Result:

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MzFNwTixJTc8vqlTSUTIyMDLUNTDTNTBUitWJVkovKlAwxC5shCmMyzBDI7guY+zCJsjCFnBhU+zCZmjCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Main Column" = _t, Date = _t]),
    #"Grouped by Date" = Table.Group(Source, {"Date"}, {{"res", each if List.PositionOf([Main Column], "Main Category")<>-1 then _ else null}}),
    #"Expanded Rows" = let cols=Table.ColumnNames(Source) in Table.ExpandTableColumn(Table.SelectRows(Table.RemoveColumns(#"Grouped by Date", "Date"), each not([res] is null)), "res", cols, cols)
in
    #"Expanded Rows"

[Vibration85]

May I ask if I want to add another column to have the condition based on that as well, how can I extend this query?  I have another column called category_id, and I want to check if we have the Main category check the category_id of all these group1, group2, ... and if their category_ids is the same as the Main category then accumulate prices of them:

For the above example if group 1, group 2 has the same category_id as the Main category, 

I need to sum up for Feb 12th prices of Main category + group 1 + group 2 = 1290+340+367

[DataInsights]

@Vibration85,

You can use Group By in Power Query. See step GroupRows:

let
  Source = OriginalTable,
  GroupByDate = Table.Group(
    Source,
    {"Date"},
    {{"res", each if List.PositionOf([Main column], "Main Category") <> - 1 then _ else null}}
  ),
  ExpandRows =
    let
      cols = Table.ColumnNames(Source)
    in
      Table.ExpandTableColumn(
        Table.SelectRows(Table.RemoveColumns(GroupByDate, "Date"), each not ([res] is null)),
        "res",
        cols,
        cols
      ),
  ChangeType = Table.TransformColumnTypes(
    ExpandRows,
    {{"ID", Int64.Type}, {"Date", type date}, {"Price", Int64.Type}}
  ),
  GroupRows = Table.Group(
    ChangeType,
    {"Date", "category_id"},
    {{"Sum Price", each List.Sum([Price]), type number}}
  )
in
  GroupRows

[Vibration85]

@DataInsights   thanks a lot, it works well and now I learned how can I group-by a table with one or more conditions ... that was what exactly I wanted 🙂 

[Vibration85]

Many thanks, it works well 🙂 

[DataInsights · Accepted solution]

@Vibration85,

Try this solution in Power Query.

1. Create table MainCategoryDates, consisting of distinct dates for "Main Category" rows.

let
    Source = OriginalTable,
    FilterRows = Table.SelectRows(Source, each ([Main column] = "Main Category")),
    RemoveColumns = Table.SelectColumns(FilterRows,{"Date"}),
    RemoveDuplicates = Table.Distinct(RemoveColumns)
in
    RemoveDuplicates

2. Merge table MainCategoryDates with the data table using an inner join on the Date column.

let
    Source = Table.NestedJoin(OriginalTable, {"Date"}, MainCategoryDates, {"Date"}, "MainCategoryDates", JoinKind.Inner),
    RemoveColumns = Table.RemoveColumns(Source,{"MainCategoryDates"})
in
    RemoveColumns

3. Result:

[Vibration85]

Thank you, it works well for both solutions 🙂